package com.lenovo.lcdm.dcm.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.lenovo.lcdm.common.enums.CommonMsgEnum;
import com.lenovo.lcdm.dcm.common.constant.CommonConstant;
import com.lenovo.lcdm.dcm.common.enums.BusinessMsgEnum;
import com.lenovo.lcdm.dcm.common.exception.DcmBusinessException;
import com.lenovo.lcdm.dcm.common.util.ContextUtil;
import com.lenovo.lcdm.dcm.constant.DcmConstant;
import com.lenovo.lcdm.dcm.dto.*;
import com.lenovo.lcdm.dcm.entity.*;
import com.lenovo.lcdm.dcm.service.*;
import com.lenovo.lcdm.dcm.vo.DcmFullCvVo;
import com.lenovo.lcdm.dcm.vo.DcmHistoryVo;
import com.lenovo.lcdm.dcm.vo.DummyConfigurableModelVo;
import com.lenovo.lcdm.integration.utils.KafkaHelper;
import com.lenovo.lcdm.team.dto.UserAuthDto;
import com.lenovo.lcdm.team.entity.TeamGroup;
import com.lenovo.lcdm.team.service.ITeamUserService;
import com.lenovo.lcdm.type.service.TypeAttributeExtService;
import com.lenovo.lcdm.type.vo.LocalAttributeModel;
import com.lenovo.lcdm.type.vo.TypeDefinitionModel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class DcmServiceImpl implements DcmService {

    private final DummyConfigurableModelService dummyConfigurableModelService;
    private final DummyConfigurableModelMasterService dummyConfigurableModelMasterService;
    private final DummyConfigurableModelCategoryService dummyConfigurableModelCategoryService;
    private final DummyConfigurableModelProductFeaturesDescService dummyConfigurableModelProductFeaturesDescService;
    private final PortfolioOfferingService portfolioOfferingService;
    private final DcmBusinessConfigurationKeywordsService dcmBusinessConfigurationKeywordsService;
    private final GvService gvService;
    private final DcmBusinessConfigurationCategoryCService dcmBusinessConfigurationCategoryCService;
    private final DcmAttachmentLinkService dcmAttachmentLinkService;
    private final CtoCVService ctoCVService;
    private final ITeamUserService iTeamUserService;
    private final ConfigurationGeoMappingService configurationGeoMappingService;
    private final TypeAttributeExtService typeAttributeExtService;
    private final ConfigurationGeoCountryMappingService configurationGeoCountryMappingService;

    private static final String SHEET_NAME_FEATURE = "Features";

    private static final String CHECK_MSG_LINE = "Line ";

    private static final String CHECK_MSG_COLUMN = " column ";

    private static final String CTO_PREFIX = "CTO_PREFIX_";

    public static final String NAME_GROUP_ITADMIN = "GROUP_IT_ADMIN";
    public static final String NAME_GROUP_SUFFIX_EDITOR = "_Editor";

    @Autowired
    public DcmServiceImpl(DummyConfigurableModelService dummyConfigurableModelService,
                          DummyConfigurableModelMasterService dummyConfigurableModelMasterService,
                          DummyConfigurableModelCategoryService dummyConfigurableModelCategoryService,
                          DummyConfigurableModelProductFeaturesDescService dummyConfigurableModelProductFeaturesDescService,
                          PortfolioOfferingService portfolioOfferingService,
                          DcmBusinessConfigurationKeywordsService dcmBusinessConfigurationKeywordsService,
                          GvService gvService,
                          DcmBusinessConfigurationCategoryCService dcmBusinessConfigurationCategoryCService,
                          DcmAttachmentLinkService dcmAttachmentLinkService,
                          CtoCVService ctoCVService,
                          ITeamUserService iTeamUserService,
                          ConfigurationGeoMappingService configurationGeoMappingService,
                          TypeAttributeExtService typeAttributeExtService,
                          ConfigurationGeoCountryMappingService configurationGeoCountryMappingService) {
        this.dummyConfigurableModelService = dummyConfigurableModelService;
        this.dummyConfigurableModelMasterService = dummyConfigurableModelMasterService;
        this.dummyConfigurableModelCategoryService = dummyConfigurableModelCategoryService;
        this.dummyConfigurableModelProductFeaturesDescService = dummyConfigurableModelProductFeaturesDescService;
        this.portfolioOfferingService = portfolioOfferingService;
        this.dcmBusinessConfigurationKeywordsService = dcmBusinessConfigurationKeywordsService;
        this.gvService = gvService;
        this.dcmBusinessConfigurationCategoryCService = dcmBusinessConfigurationCategoryCService;
        this.dcmAttachmentLinkService = dcmAttachmentLinkService;
        this.ctoCVService = ctoCVService;
        this.iTeamUserService = iTeamUserService;
        this.configurationGeoMappingService = configurationGeoMappingService;
        this.typeAttributeExtService = typeAttributeExtService;
        this.configurationGeoCountryMappingService = configurationGeoCountryMappingService;
    }

    @Override
    @Transactional
    public Long createDcm(DummyConfigurableModelDto dummyConfigurableModelDto) {
        return dummyConfigurableModelService.createDcm(dummyConfigurableModelDto);
    }

    @Override
    public IPage<DummyConfigurableModelVo> getDcms(String keywords, Integer pageNo, Integer pageSize) {
        List<String> geos = getGeos();
        if (CollectionUtil.isEmpty(geos)) {
            geos = Arrays.stream(DcmConstant.GEO).toList();
        }
        List<String> resultGeos = Lists.newArrayList();
        List<String> params = Lists.newArrayList();
        if (StringUtils.isNotBlank(keywords)) {
            for (String keyword : keywords.split(",")) {
                List<String> cloneGeos = Lists.newArrayList(geos);
                if (StringUtils.isNotBlank(keyword)) {
                    cloneGeos.retainAll(Lists.newArrayList(StringUtils.upperCase(keyword)));
                }
                if (CollectionUtil.isNotEmpty(cloneGeos)) {
                    resultGeos.addAll(cloneGeos);
                }
                if (Arrays.stream(DcmConstant.GEO).noneMatch(geo -> geo.equals(StringUtils.upperCase(keyword)))) {
                    params.add(keyword);
                }
            }
        }
        if (CollectionUtil.isEmpty(resultGeos)) {
            resultGeos.addAll(geos);
        }
        resultGeos = resultGeos.stream().distinct().toList();
        Integer count = dummyConfigurableModelMasterService.selectDcmsCount(params, resultGeos);
        List<DummyConfigurableModelVo> dummyConfigurableModelVos = dummyConfigurableModelMasterService.selectDcms(params, resultGeos, (pageNo - 1) * pageSize, pageSize);
        IPage<DummyConfigurableModelVo> pageVo = new Page<>();
        pageVo.setCurrent(pageNo);
        pageVo.setSize(pageSize);
        pageVo.setTotal(count);
        pageVo.setRecords(dummyConfigurableModelVos);
        return pageVo;
    }

    private List<String> getGeos() {
        UserAuthDto userAuthorities = iTeamUserService.getUserAuthorities(ContextUtil.getCurrentUser(), false);
        List<ConfigurationGeoMapping> list = configurationGeoMappingService.list();
        List<TeamGroup> groups = userAuthorities.getGroups();
        return Lists.newArrayList(list.stream()
                .filter(geoMapping -> groups.stream().map(TeamGroup::getGroupName).toList().contains(geoMapping.getGroupName()))
                .map(ConfigurationGeoMapping::getGeo)
                .filter(value -> !"*".equals(value)).toList());
    }

    @Override
    @Transactional
    public void checkIn(DummyConfigurableModelDto dummyConfigurableModelDto) {
        // 保存link
        DummyConfigurableModelDto originModelDto = getDcmDtoById(dummyConfigurableModelDto.getId().intValue());
        if (originModelDto == null) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(dummyConfigurableModelDto.getId().intValue());
        if (!dummyConfigurableModel.getLastestAll()) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_IS_NOT_LASTEST);
        }
        List<String> ctos = Arrays.stream(dummyConfigurableModelDto.getReferenceCtoNumber().split(",")).map(String::trim).distinct().toList();
        String ctoNumberString = CollectionUtil.join(ctos, ",");
        List<CtoCV> ctoCVS = ctoCVService.list(Wrappers.lambdaQuery(CtoCV.class).in(CtoCV::getCtoNumber, ctos));

        ctos.stream().filter(ctoNumber -> !ctoCVS.stream().map(CtoCV::getCtoNumber).toList().contains(ctoNumber)).findAny().ifPresent(s -> {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_CTO_NUMBER_NOT_EXIST);
        });
        dummyConfigurableModelDto.setReferenceCtoNumber(ctoNumberString);
        dummyConfigurableModelDto.setBusinessSegment(originModelDto.getBusinessSegment());
        dummyConfigurableModelDto.setProductSegment(originModelDto.getProductSegment());
        DummyConfigurableModelDto resultDto = handelDataNew(dummyConfigurableModelDto, false);
        dummyConfigurableModelService.saveLinkNew(resultDto);
        // 保存本身, 并变更状态
        if (StringUtils.isBlank(dummyConfigurableModelDto.getVersionRemark())) {
            dummyConfigurableModelDto.setVersionRemark("Updated by Edit action");
        }
        dummyConfigurableModelService.checkIn(dummyConfigurableModelDto);
    }

    private DummyConfigurableModelDto handelDataNew(DummyConfigurableModelDto newModelDto, Boolean isRefresh) {
        DummyConfigurableModelDto originModelDto;
        if (newModelDto.getId() == null) {
            originModelDto = BeanUtil.toBean(newModelDto, DummyConfigurableModelDto.class);
            originModelDto.setDummyConfigurableModelCatagoryDtos(Lists.newArrayList());
        } else {
            originModelDto = dummyConfigurableModelService.getDcmDtoById(dummyConfigurableModelService.getById(newModelDto.getId()));
        }
        if (isRefresh) {
            originModelDto = dummyConfigurableModelService.getDcmDtoById(dummyConfigurableModelService.getById(newModelDto.getId()));
            for (DummyConfigurableModelCatagoryDto dummyConfigurableModelCatagoryDto : originModelDto.getDummyConfigurableModelCatagoryDtos()) {
                dummyConfigurableModelCatagoryDto.getDummyConfigurableModelFeatureDtos().removeIf(dummyConfigurableModelFeatureDto -> !DcmConstant.DCM_FEATURE_COLOR_BLACK.equals(dummyConfigurableModelFeatureDto.getFinalColor()));
            }
        }
        // 1. 删除link
        List<DummyConfigurableModelCatagoryDto> removeCategoryList = Lists.newArrayList();
        List<DummyConfigurableModelFeatureDto> removeFeatureList = Lists.newArrayList();
        originModelDto.getDummyConfigurableModelCatagoryDtos().forEach(category -> {
            DummyConfigurableModelCatagoryDto newCategory = newModelDto.getDummyConfigurableModelCatagoryDtos().stream().filter(item -> item.getCategory().equals(category.getCategory())).findFirst().orElse(null);
            if (newCategory == null) {
                removeCategoryList.add(category);
            } else {
                category.getDummyConfigurableModelFeatureDtos().forEach(feature -> {
                    DummyConfigurableModelFeatureDto dummyConfigurableModelFeatureDto = newCategory.getDummyConfigurableModelFeatureDtos().stream().filter(item -> Objects.equals(item.getId(), feature.getId()) && !item.getDescName().equals(feature.getDescName())).findFirst().orElse(null);
                    if (dummyConfigurableModelFeatureDto != null) {
                        feature.setDescName(dummyConfigurableModelFeatureDto.getDescName());
                        if (dummyConfigurableModelFeatureDto.getFinalCvs().equals(feature.getFinalCvs())
                                && !DcmConstant.DCM_FEATURE_COLOR_BLACK.equals(dummyConfigurableModelFeatureDto.getFinalColor())) {
                            dummyConfigurableModelFeatureDto.setFinalCvs(null);
                        }
                    }
                    DummyConfigurableModelFeatureDto newFeature = newCategory.getDummyConfigurableModelFeatureDtos().stream().filter(item -> item.getDescName().equals(feature.getDescName())).findFirst().orElse(null);
                    if (newFeature == null) {
                        removeFeatureList.add(feature);
                    }
                });
                category.getDummyConfigurableModelFeatureDtos().removeAll(removeFeatureList);
            }
        });

        originModelDto.getDummyConfigurableModelCatagoryDtos().removeAll(removeCategoryList);

        // 2.获取公共计算需要的参数 begin
        // 获取可以匹配的cv值
        List<CtoCVsDto> allCvsDtos = ctoCVService.getCVListByCtoNamesAndProductSegment(newModelDto.getReferenceCtoNumber(), newModelDto.getProductSegment(), newModelDto.getGeo());

        // 获取可以匹配的gv值
        List<Gv> allGvs = gvService.lambdaQuery().in(Gv::getStatus, Lists.newArrayList("New", "Released")).list();
        List<String> gvs = new ArrayList<>(allGvs.stream()
                .filter(gv -> newModelDto.getProductSegment().equals(gv.getProductSegment())
                        && ("SMB".equals(newModelDto.getBusinessSegment()) || gv.getBusinessSegment().equals(newModelDto.getBusinessSegment())))
                .map(Gv::getGlobalValue).toList());
        // 获取可以匹配的Keyword值
        List<DcmBusinessConfigurationKeywords> allKeywords = dcmBusinessConfigurationKeywordsService.getAll();
        if (CollectionUtil.isEmpty(allKeywords)) {
            allKeywords = Lists.newArrayList();
        }
        List<DcmBusinessConfigurationKeywords> keywords = allKeywords.stream()
                .filter(keyword -> keyword.getBusinessSegment() == null || "SMB".equals(newModelDto.getBusinessSegment()) || keyword.getBusinessSegment().equals(newModelDto.getBusinessSegment()))
                .filter(keyword -> keyword.getProductSegment() == null || keyword.getProductSegment().equals(newModelDto.getProductSegment()))
                .peek(keyword -> {
                    keyword.setOriginalValue(StringUtils.upperCase(keyword.getOriginalValue()));
                    keyword.setCvReplacementValue(StringUtils.upperCase(keyword.getCvReplacementValue()));
                    keyword.setGvReplacementValue(StringUtils.upperCase(keyword.getGvReplacementValue()));
                }).toList();
        // 获取可以匹配的geoCountryMapping值
        List<ConfigurationGeoCountryMapping> configurationGeoCountryMappings = configurationGeoCountryMappingService.list();
        configurationGeoCountryMappings = configurationGeoCountryMappings.stream().peek(item -> {
            if (!item.getCountrySuffix().startsWith("_")) {
                item.setCountrySuffix("_" + item.getCountrySuffix());
            }
        }).toList();
        List<String> geos = getGeos();
        if (CollectionUtil.isEmpty(geos)) {
            geos.addAll(Arrays.stream(DcmConstant.GEO).toList());
        }
        Map<String, List<String>> suffixFullMap = configurationGeoCountryMappings.stream()
                .filter(item -> StringUtils.isBlank(item.getGeo()))
                .collect(Collectors.groupingBy(ConfigurationGeoCountryMapping::getCategory, Collectors.mapping(ConfigurationGeoCountryMapping::getCountrySuffix, Collectors.toList())));
        Map<String, List<String>> suffixMap = configurationGeoCountryMappings.stream()
                .filter(item -> geos.contains(item.getGeo()))
                .collect(Collectors.groupingBy(ConfigurationGeoCountryMapping::getCategory, Collectors.mapping(ConfigurationGeoCountryMapping::getCountrySuffix, Collectors.toList())));
        // 获取公共计算需要的参数 end
        // 3. 添加或修改link
        DummyConfigurableModelDto finalOriginModelDto = originModelDto;
        newModelDto.getDummyConfigurableModelCatagoryDtos().forEach(category -> {
            DummyConfigurableModelCatagoryDto originCategory = finalOriginModelDto.getDummyConfigurableModelCatagoryDtos().stream().filter(item -> item.getCategory().equals(category.getCategory())).findFirst().orElse(null);
            // 根据category获取范围
            List<DcmBusinessConfigurationKeywords> filterKeywords = keywords.stream().filter(keyword -> keyword.getCategory().equals(category.getCategory())).toList();
            List<CtoCVsDto> cvMap = allCvsDtos.stream()
                    .filter(ctoCVsDto -> ctoCVsDto.getCategory().equals(category.getCategory()))
                    .distinct()
                    .toList();
            List<String> cvStringList = allCvsDtos.stream()
                    .filter(ctoCVsDto -> ctoCVsDto.getCategory().equals(category.getCategory()))
                    .map(CtoCVsDto::getValues).flatMap(List::stream)
                    .distinct()
                    .toList();
            List<String> allCountries = suffixFullMap.get(category.getCategory());
            List<String> countries = suffixMap.get(category.getCategory());

            List<String> gvsStringList = gvs.stream().filter(value -> {
                if (CollectionUtil.isEmpty(allCountries) || allCountries.stream().noneMatch(value::endsWith)) {
                    return true;
                }
                if (allCountries.stream().anyMatch(value::endsWith)) {
                    List<String> list = allCountries.stream().filter(value::endsWith).toList();
                    Optional<String> max = list.stream().max(Comparator.comparing(String::length));
                    if (max.isPresent()) {
                        String country = max.get();
                        return CollectionUtil.isNotEmpty(countries) && new HashSet<>(countries).contains(country);
                    }
                }
                return false;
            }).toList();

            if (originCategory == null) { // add 计算cv gv final
                DummyConfigurableModelCatagoryDto newCategory = new DummyConfigurableModelCatagoryDto();
                newCategory.setCategory(category.getCategory());
                newCategory.setFullCvs(cvMap);
                category.getDummyConfigurableModelFeatureDtos().forEach(feature -> {
                    DummyConfigurableModelFeatureDto newFeature = new DummyConfigurableModelFeatureDto();
                    newFeature.setDescName(feature.getDescName());
                    computingCvAndGv(newFeature, filterKeywords, cvStringList, gvsStringList);
                    if (StringUtils.isNotBlank(feature.getFinalCvs())) {
                        newFeature.setFinalCvs(feature.getFinalCvs());
                        if ((StringUtils.isBlank(feature.getReferenceCtoCv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getReferenceCtoCv(), ",")), feature.getFinalCvs()))
                                && (StringUtils.isBlank(feature.getRecommendedGv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getRecommendedGv(), ",")), feature.getFinalCvs()))) {
                            newFeature.setFinalColor("black");
                        }
                    }
                    if (StringUtils.isBlank(newFeature.getFinalCvs()) && StringUtils.isBlank(feature.getFinalCvs())) {
                        newFeature.setFinalCvs(feature.getDescName());
                    }
                    if (CollectionUtil.isNotEmpty(newCategory.getDummyConfigurableModelFeatureDtos())) {
                        newCategory.getDummyConfigurableModelFeatureDtos().add(newFeature);
                    } else {
                        newCategory.setDummyConfigurableModelFeatureDtos(Lists.newArrayList(newFeature));
                    }
                });
                finalOriginModelDto.getDummyConfigurableModelCatagoryDtos().add(newCategory);
            } else { // update
                originCategory.setFullCvs(cvMap);
                category.getDummyConfigurableModelFeatureDtos().forEach(feature -> {
                    DummyConfigurableModelFeatureDto originFeature = originCategory.getDummyConfigurableModelFeatureDtos().stream().filter(item -> item.getDescName().equals(feature.getDescName())).findFirst().orElse(null);
                    if (originFeature == null) { // add
                        DummyConfigurableModelFeatureDto newFeature = new DummyConfigurableModelFeatureDto();
                        newFeature.setDescName(feature.getDescName());
                        computingCvAndGv(newFeature, filterKeywords, cvStringList, gvsStringList);
                        if (StringUtils.isNotBlank(feature.getFinalCvs())) {
                            newFeature.setFinalCvs(feature.getFinalCvs());
                            if ((StringUtils.isBlank(feature.getReferenceCtoCv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getReferenceCtoCv(), ",")), feature.getFinalCvs()))
                                    && (StringUtils.isBlank(feature.getRecommendedGv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getRecommendedGv(), ",")), feature.getFinalCvs()))) {
                                newFeature.setFinalColor("black");
                            }
                        }
                        if (StringUtils.isBlank(newFeature.getFinalCvs()) && StringUtils.isBlank(feature.getFinalCvs())) {
                            newFeature.setFinalCvs(feature.getDescName());
                        }
                        originCategory.getDummyConfigurableModelFeatureDtos().add(newFeature);
                    } else { // update
                        DummyConfigurableModelFeatureDto newFeature = new DummyConfigurableModelFeatureDto();
                        newFeature.setDescName(feature.getDescName());
                        computingCvAndGv(newFeature, filterKeywords, cvStringList, gvsStringList);
                        if (originFeature.getFinalCvs().equals(feature.getFinalCvs())) { // 计算cv gv 直接存原来的 final
                            originFeature.setGvColor(newFeature.getGvColor());
                            originFeature.setRecommendedGv(newFeature.getRecommendedGv());
                            originFeature.setReferenceCtoCv(newFeature.getReferenceCtoCv());
                            originFeature.setGvItemId(newFeature.getGvItemId());
                        } else if (StringUtils.isBlank(feature.getFinalCvs())) {
                            originFeature.setGvColor(newFeature.getGvColor());
                            originFeature.setRecommendedGv(newFeature.getRecommendedGv());
                            originFeature.setReferenceCtoCv(newFeature.getReferenceCtoCv());
                            originFeature.setGvItemId(newFeature.getGvItemId());
                            originFeature.setFinalCvs(newFeature.getFinalCvs());
                            originFeature.setFinalColor(newFeature.getFinalColor());
                        } else { // 计算cv gv // 保存新的final
                            originFeature.setFinalCvs(feature.getFinalCvs());
                            if ((StringUtils.isBlank(feature.getReferenceCtoCv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getReferenceCtoCv(), ",")), feature.getFinalCvs()))
                                    && (StringUtils.isBlank(feature.getRecommendedGv()) || !CollectionUtil.contains(Arrays.asList(StringUtils.split(feature.getRecommendedGv(), ",")), feature.getFinalCvs()))) {
                                originFeature.setFinalColor("black");
                            } else {
                                originFeature.setFinalColor(newFeature.getFinalColor());
                            }
                            originFeature.setGvItemId(newFeature.getGvItemId());
                        }
                    }
                });
            }
        });
        return BeanUtil.toBean(originModelDto, DummyConfigurableModelDto.class);
    }

    private void computingCvAndGv(DummyConfigurableModelFeatureDto newFeature, List<DcmBusinessConfigurationKeywords> keywords, List<String> cvs, List<String> gvs) {
        // 根据keyword 获取二维数组
        String descName = StringUtils.upperCase(newFeature.getDescName());

        List<String> matchGvs = Lists.newArrayList();
        List<String> matchCvs = Lists.newArrayList();
        if (descName.startsWith(CTO_PREFIX)) {
            matchCvs.add(descName.substring(11));
        } else {
            List<List<String>> gvList = Lists.newArrayList();
            List<List<String>> cvList = Lists.newArrayList();

            keywords = keywords.stream().peek(item -> {
                if (StrUtil.isBlank(item.getGvReplacementValue())) {
                    item.setGvReplacementValue(item.getOriginalValue());
                }
                if (StrUtil.isBlank(item.getCvReplacementValue())) {
                    item.setCvReplacementValue(item.getOriginalValue());
                }
                if ("Fuzzy".equals(item.getMatchType())) {
                    item.setCvReplacementValue("@#@" + item.getCvReplacementValue());
                    item.setGvReplacementValue("@#@" + item.getGvReplacementValue());
                }
            }).toList();

            Map<String, List<DcmBusinessConfigurationKeywords>> keywordMap = keywords.stream().collect(Collectors.groupingBy(DcmBusinessConfigurationKeywords::getOriginalValue));

            List<String> list = keywordMap.keySet().stream().sorted(Comparator.comparing(String::length).reversed()).toList();
            for (String keyword : list) {
                if (descName.contains(keyword)) {
                    descName = StrUtil.replace(descName, keyword, "_");
//                    descName = descName.replaceAll(keyword, "_");
                    List<DcmBusinessConfigurationKeywords> keywordList = keywordMap.get(keyword);
                    gvList.add(keywordList.stream().map(DcmBusinessConfigurationKeywords::getGvReplacementValue).toList());
                    cvList.add(keywordList.stream().map(DcmBusinessConfigurationKeywords::getCvReplacementValue).toList());
                }
            }
            Arrays.stream(descName.split("[ _]")).filter(StrUtil::isNotBlank).forEach(item -> {
                gvList.add(Lists.newArrayList(item));
                cvList.add(Lists.newArrayList(item));
            });

            matchGvs = getCommonMatchResult(gvList, gvs);
            matchCvs = getCommonMatchResult(cvList, cvs);
        }
        String cv = CollectionUtil.join(matchCvs, ",");
        String gv = CollectionUtil.join(matchGvs, ",");
        String gvColorString = "";
        List<String> existGvs = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(matchGvs)) {
            matchGvs.forEach(gvString -> {
                List<CtoCV> ctoCVS = ctoCVService.lambdaQuery().like(CtoCV::getValue, gvString).list();
                Optional<CtoCV> cvOptional = ctoCVS.stream().filter(ctoCV -> Arrays.asList(ctoCV.getValue().split(",")).contains(gvString)).findAny();
                if (cvOptional.isPresent()) {
                    existGvs.add(gvString);
                }
            });
            gvColorString = matchGvs.stream().map(gvValue -> existGvs.contains(gvValue) ? DcmConstant.DCM_FEATURE_COLOR_BLACK : DcmConstant.DCM_FEATURE_COLOR_ORANGE).collect(Collectors.joining(","));
        }
        newFeature.setReferenceCtoCv(cv);
        newFeature.setRecommendedGv(gv);
        newFeature.setCvColor(null);
        newFeature.setGvColor(gvColorString);
        getMatchfinalValueAndColorNew(matchGvs, matchCvs, newFeature, StringUtils.upperCase(newFeature.getDescName()), existGvs);
        if (StringUtils.isNotBlank(newFeature.getFinalCvs())
                && matchGvs.contains(newFeature.getFinalCvs())) {
            newFeature.setGvItemId("gvItemId");
        }
    }

    private List<String> getCommonMatchResult(List<List<String>> originList, List<String> allList) {
        // 转换
        List<List<List<String>>> converList = Lists.newArrayList();
        for (List<String> origin : originList) {
            List<List<String>> firstList = Lists.newArrayList();
            for (String item : origin) {
                List<String> secondList = commonMatchResult(item, allList);
                firstList.add(secondList);
            }
            converList.add(firstList);
        }
        // 解析
        List<String> resutlList = Lists.newArrayList();
        List<List<String>> firstList = Lists.newArrayList();
        for (List<List<String>> first : converList) {
            firstList.add(first.stream().flatMap(List::stream).distinct().toList());
        }
        for (int i = 0; i < firstList.size(); i++) {
            if (i == 0) {
                resutlList.addAll(firstList.get(i));
            } else {
                resutlList.retainAll(firstList.get(i));
            }
        }
        return resutlList;
    }

    private List<String> commonMatchResult(String item, List<String> allList) {
        if (item.startsWith("@#@")) {
            item = item.substring(3);
            List<String> list = Arrays.stream(item.split("[ _]")).toList();
            List<String> returnList = Lists.newArrayList();
            for (int i = 0; i < list.size(); i++) {
                String value = list.get(i);
                List<String> result = Lists.newArrayList();
                if (value.contains("+")) {
                    List<String> strings = new ArrayList<>(Arrays.stream(value.split("\\+")).toList());
                    strings.add(value);
                    for (String string : strings) {
                        result.addAll(allList.stream().filter(all -> all.contains(string)).toList());
                    }
                } else {
                    result.addAll(allList.stream().filter(all -> all.contains(value)).toList());
                }
                result = result.stream().distinct().toList();
                if (i == 0) {
                    returnList.addAll(result);
                } else {
                    returnList.retainAll(result);
                }
            }
            return returnList;
        } else {
            List<String> list = Arrays.stream(item.split("[ _]")).toList();
            List<String> returnList = Lists.newArrayList();
            for (int i = 0; i < list.size(); i++) {
                String value = list.get(i);
                List<String> result = Lists.newArrayList();
                if (value.contains("+")) {
                    List<String> strings = new ArrayList<>(Arrays.stream(value.split("\\+")).toList());
                    strings.add(value);
                    for (String string : strings) {
                        result.addAll(allList.stream().filter(all -> Arrays.stream(all.split("_")).toList().stream().anyMatch(allItem -> allItem.equals(string))).toList());
                    }
                } else {
                    result.addAll(allList.stream().filter(all -> Arrays.stream(all.split("_")).toList().stream().anyMatch(allItem -> allItem.equals(value))).toList());
                }
                result = result.stream().distinct().toList();
                if (i == 0) {
                    returnList.addAll(result);
                } else {
                    returnList.retainAll(result);
                }
            }
            return returnList;
        }
    }

    private List<String> getMatchResult(List<String> originFeatureKeyWords, List<String> cvs, List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords, String resultType) {
        // 2. 循环feature每个词
        List<String> returnCvs = Lists.newArrayList(cvs);
        for (String featureWord : originFeatureKeyWords) {
            List<String> retainCvs = Lists.newArrayList();
            if (featureWord.contains("+")) {
                List<String> splitFeatureWords = Lists.newArrayList(Arrays.stream(featureWord.split("\\+")).toList());
                splitFeatureWords.add(featureWord);
                for (String splitFeatureWord : splitFeatureWords) {
                    List<DcmBusinessConfigurationKeywords> keywords;
                    if ("CV".equals(resultType)) {
                        keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> StringUtils.isNotBlank(splitFeatureWord) && splitFeatureWord.equals(keyword.getCvReplacementValue())).toList();
                    } else {
                        keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> StringUtils.isNotBlank(splitFeatureWord) && splitFeatureWord.equals(keyword.getGvReplacementValue())).toList();
                    }
                    retainCvs.addAll(matchResult(returnCvs, keywords, splitFeatureWord, resultType));
                    CollectionUtil.distinct(retainCvs);
                }
            } else {
                List<DcmBusinessConfigurationKeywords> keywords;
                if ("CV".equals(resultType)) {
                    keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> StringUtils.isNotBlank(featureWord) && featureWord.equals(keyword.getCvReplacementValue())).toList();
                } else {
                    keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> StringUtils.isNotBlank(featureWord) && featureWord.equals(keyword.getGvReplacementValue())).toList();
                }
                retainCvs = matchResult(returnCvs, keywords, featureWord, resultType);
            }

            returnCvs.retainAll(retainCvs);
        }
        return returnCvs;
    }

    private List<String> matchResult(List<String> returnString, List<DcmBusinessConfigurationKeywords> keywords, String featureWord, String resultType) {
        if (CollectionUtil.isEmpty(keywords)) { // 如果小范围的keywords 为空 或者 未匹配到关键字 则用原词 精准匹配
            return returnString.stream().filter(result -> Arrays.stream(result.split(" |_")).toList().contains(featureWord)).toList();
        } else { // 如果匹配到keywords 则看匹配到的按匹配类型 根据替换词 查找结果
            DcmBusinessConfigurationKeywords dcmBusinessConfigurationKeywords = keywords.get(0);
            if ("Exact".equals(dcmBusinessConfigurationKeywords.getMatchType())) {
                if ("CV".equals(resultType)) {
                    return returnString.stream().filter(result -> Arrays.stream(result.split(" |_")).toList().contains(dcmBusinessConfigurationKeywords.getCvReplacementValue())).toList();
                } else {
                    return returnString.stream().filter(result -> Arrays.stream(result.split(" |_")).toList().contains(dcmBusinessConfigurationKeywords.getGvReplacementValue())).toList();
                }

            } else {
                if ("CV".equals(resultType)) {
                    return returnString.stream().filter(result ->
                            Arrays.stream(result.split(" |_")).anyMatch(splitCv -> splitCv.contains(dcmBusinessConfigurationKeywords.getCvReplacementValue()))).toList();
                } else {
                    return returnString.stream().filter(result ->
                            Arrays.stream(result.split(" |_")).anyMatch(splitCv -> splitCv.contains(dcmBusinessConfigurationKeywords.getGvReplacementValue()))).toList();
                }

            }
        }
    }

    private void getMatchfinalValueAndColorNew(List<String> matchGvs, List<String> matchCvs, DummyConfigurableModelFeatureDto dto, String desc, List<String> existGvs) {
        if (matchGvs.size() == 1) {
            if (existGvs.contains(matchGvs.get(0))) {
                dto.setFinalCvs(matchGvs.get(0));
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
            } else {
                if (matchCvs.size() == 1) {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                } else if (matchCvs.size() == 0) {
                    dto.setFinalCvs(matchGvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
                } else {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            }
        } else if (matchGvs.size() == 0) {
            if (matchCvs.size() == 1) {
                dto.setFinalCvs(matchCvs.get(0));
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
            } else if (matchCvs.size() == 0) {
                dto.setFinalCvs(desc);
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
            } else {
                dto.setFinalCvs(matchCvs.get(0));
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
            }
        } else {
            List<String> gvs = matchGvs.stream().filter(existGvs::contains).toList();
            if (CollectionUtil.isEmpty(gvs)) {
                if (matchCvs.size() == 1) {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                } else if (matchCvs.size() == 0) {
                    dto.setFinalCvs(matchGvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
                } else {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            } else {
                if (matchCvs.size() > 0) {
                    List<String> commonGvs = gvs.stream().filter(matchCvs::contains).toList();
                    if (commonGvs.size() == 1) {
                        dto.setFinalCvs(commonGvs.get(0));
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                    } else if (commonGvs.size() > 1) {
                        dto.setFinalCvs(commonGvs.get(0));
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                    } else {
                        dto.setFinalCvs(matchGvs.get(0));
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                    }
                } else {
                    dto.setFinalCvs(gvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            }
        }
    }

    private List<DcmLoadsheetDto> transferModelDtoToLoadsheetDto(DummyConfigurableModelDto dummyConfigurableModelDto) {
        List<DcmLoadsheetDto> dtos = Lists.newArrayList();
        List<DummyConfigurableModelCatagoryDto> categories = dummyConfigurableModelDto.getDummyConfigurableModelCatagoryDtos();
        categories.forEach(category -> {
            List<DummyConfigurableModelFeatureDto> dummyConfigurableModelFeatureDtos = category.getDummyConfigurableModelFeatureDtos();
            List<DcmLoadsheetDto> dtosByCategory = dummyConfigurableModelFeatureDtos.stream().map(feature -> {
                DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
                dcmLoadsheetDto.setDcmId(dummyConfigurableModelDto.getId());
                dcmLoadsheetDto.setCategoryId(category.getId());
                dcmLoadsheetDto.setFeatureId(feature.getId());
                dcmLoadsheetDto.setOfferingName(dummyConfigurableModelDto.getName());
                dcmLoadsheetDto.setCategory(category.getCategory());
                dcmLoadsheetDto.setFeature(feature.getDescName());
                dcmLoadsheetDto.setCtoNumber(dummyConfigurableModelDto.getReferenceCtoNumber());
                return dcmLoadsheetDto;
            }).toList();
            dtos.addAll(dtosByCategory);
        });
        return dtos;
    }

    @Override
    public DummyConfigurableModelVo getDcmById(Integer id) {
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        return dummyConfigurableModelService.getDcmById(dummyConfigurableModel);
    }

    @Override
    @Transactional
    public void undoCheckout(String ids) {
        String[] split = ids.split(",");
        List<Long> list = Arrays.stream(split).map(Long::valueOf).toList();
        if (CollectionUtil.isEmpty(list)) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        List<DummyConfigurableModel> dcms = Lists.newArrayList();
        for (Long id : list) {
            DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
            if (dummyConfigurableModel == null) {
                throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
            }
            if (!dummyConfigurableModel.getLastestAll()) {
                throw new DcmBusinessException(BusinessMsgEnum.DCM_IS_NOT_LASTEST);
            }
            if (StringUtils.isBlank(dummyConfigurableModel.getCheckoutBy())) {
                throw new DcmBusinessException(BusinessMsgEnum.DCM_UNDOCHECKOUT_ITEM_NOT_CHECKOUT);
            }
            String itcode = ContextUtil.getCurrentUser();
            // 是否有it admin权限
            UserAuthDto userAuthorities = iTeamUserService.getUserAuthorities(itcode, false);
            List<TeamGroup> groups = userAuthorities.getGroups();
            if (groups.stream().noneMatch(group -> "GROUP_IT_ADMIN".equals(group.getGroupName()))) {
                // 需要本人操作
                if (!ContextUtil.getCurrentUser().equals(dummyConfigurableModel.getCheckoutBy())) {
                    throw new DcmBusinessException(BusinessMsgEnum.DCM_UNDOCHECKOUT_NEED_CHECKOUTBY);
                }
            }
            dcms.add(dummyConfigurableModel);
        }
        dcms.forEach(dummyConfigurableModelService::undoCheckout);
    }

    @Override
    public List<DcmHistoryVo> getDcmHistoryById(Integer id) {
        if (id == null) {
            throw new DcmBusinessException("");
        }
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            throw new DcmBusinessException("");
        }
        return dummyConfigurableModelService.getDcmHistoryByMasterId(dummyConfigurableModel.getMasterId());
    }

    @Override
    @Transactional
    public List<Long> publicDcmByIds(String ids) {
        String[] split = ids.split(",");
        List<Long> list = Arrays.stream(split).map(Long::valueOf).toList();
        List<DummyConfigurableModel> dummyConfigurableModels = dummyConfigurableModelService.lambdaQuery().in(DummyConfigurableModel::getId, list).list();
        precheck(list, dummyConfigurableModels);
        List<Long> returnIds = Lists.newArrayList();
        dummyConfigurableModels.forEach(dummyConfigurableModel -> {
            // 1. 升版, 并把state变为Published
            Integer newId = dummyConfigurableModelService.endVersion(dummyConfigurableModel);
            returnIds.add(newId.longValue());
            // 2. 合成数据 并发送
            DummyConfigurableModelMaster dummyConfigurableModelMaster = dummyConfigurableModelMasterService.getById(dummyConfigurableModel.getMasterId());
            DummyConfigurableModel newDummyConfigurableModel = dummyConfigurableModelService.getById(newId);
            Map<String, Object> map = Maps.newHashMap();
            map.put("number", dummyConfigurableModelMaster.getNumber());
            map.put("name", dummyConfigurableModelMaster.getName());
            map.put("GEO", newDummyConfigurableModel.getGeo());
            map.put("state", newDummyConfigurableModel.getState());

            map.put("refCTONumber", Arrays.stream(newDummyConfigurableModel.getReferenceCtoNumber().split(",")).toList().get(0));
            map.put("productSegment", newDummyConfigurableModel.getProductSegment());
            map.put("businessSegment", newDummyConfigurableModel.getBusinessSegment());
            map.put("version", newDummyConfigurableModel.getVersion() + "." + newDummyConfigurableModel.getIteration());
            List<DummyConfigurableModelCategory> categories =
                    dummyConfigurableModelCategoryService.lambdaQuery().eq(DummyConfigurableModelCategory::getDcmId, dummyConfigurableModel.getId()).list();
            if (categories != null && !categories.isEmpty()) {
                List<Map<String, Object>> features = Lists.newArrayList();
                categories.forEach(category -> {
                    List<DummyConfigurableModelProductFeaturesDesc> featuresDescs =
                            dummyConfigurableModelProductFeaturesDescService.lambdaQuery().eq(DummyConfigurableModelProductFeaturesDesc::getCategoryId, category.getId()).list();
                    featuresDescs.forEach(featuresDesc -> {
                        Map<String, Object> feature = Maps.newHashMap();
                        feature.put("name", category.getCategoryName());
                        feature.put("featureDesc", featuresDesc.getDescName());
                        feature.put("finalCVsValue", featuresDesc.getFinalCvs());
                        feature.put("colorIndicator", featuresDesc.getFinalColor());
                        feature.put("isFromGv", StringUtils.isNotBlank(featuresDesc.getGvItemId()));
                        features.add(feature);
                    });

                });
                map.put("category", features);
            }
            KafkaHelper.publish("dcm", JSONUtil.toJsonStr(map));
//             pulish 数据
//            dcmProducer.send(JSONUtil.toJsonStr(map));
        });
        return returnIds;
    }

    /**
     * Dcm loadsheet 内容校验
     *
     * @param file  - 文件本身
     * @param dtos  - 已解析的数据(不包含EOF之后的数据)
     * @param lists - 已解析的数据(所有)
     * @throws IOException
     */
    public void checkLoadsheet(MultipartFile file, List<DcmLoadsheetDto> dtos, List<List<Object>> lists) throws IOException {
        // 解析loadsheet
        Workbook workbook = null;
        try (InputStream is = file.getInputStream();) {
            if (file.getOriginalFilename().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else if (file.getOriginalFilename().endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            } else {
                throw new DcmBusinessException("File Type error.Please upload excel file.", CommonMsgEnum.FAIL.getCode());
            }
            Sheet sheet = workbook.getSheet(SHEET_NAME_FEATURE);
            if (sheet == null) {
                throw new DcmBusinessException("Cannot find sheet:" + SHEET_NAME_FEATURE + ".Please check.", CommonMsgEnum.FAIL.getCode());
            }

            //读取Geo
            Row geoRow = sheet.getRow(1);
            Cell geoCell = geoRow.getCell(1);
            String geo = geoCell.getStringCellValue();

            StringBuilder builder = new StringBuilder();
            if (StringUtils.isEmpty(geo)) {
                builder.append(CHECK_MSG_LINE).append(geoCell.getRowIndex() + 1).append(CHECK_MSG_COLUMN).append(geoCell.getColumnIndex() + 1).append(" : GEO can not be empty, please select from drop-down list first.\n");
            }
            if (!getOptions(sheet, 1, 1).contains(geo)) {
                builder.append(CHECK_MSG_LINE).append(geoCell.getRowIndex() + 1).append(CHECK_MSG_COLUMN).append(geoCell.getColumnIndex() + 1).append(" : GEO (").append(geo).append(") is not valid, please select from drop-down list.\n");
            }

            // 没有#EOF,提示错误消息
            boolean hasEof = lists.stream().anyMatch(data -> data.get(0) != null && String.valueOf(data.get(0)).equals("#EOF"));
            // 数据为空,提示错误消息
            if (CollectionUtils.isEmpty(dtos) && hasEof) {
                builder.append("There is NO data input before #EOF, Please check and reupload.\n");
            } else if (CollectionUtils.isEmpty(dtos) && !hasEof) {
                builder.append("There is NO data input, Please check and reupload.\n");
            }
            //数据为空不再往下执行
            if (CollectionUtils.isEmpty(dtos)) {
                throw new DcmBusinessException(builder.toString(), CommonMsgEnum.FAIL.getCode());
            }

            Map<String, List<DcmLoadsheetDto>> dtoMap = dtos.stream().collect(Collectors.groupingBy(DcmLoadsheetDto::getOfferingName));


            //所有数据根据portfolio offering name分组，统计不重复的cto
            Map<String, Set<String>> nameCtMap = new HashMap<>();
            dtoMap.forEach((key, value) -> {
                Set<String> ctosTemp = value.stream().filter(item -> StringUtils.isNotEmpty(item.getCtoNumber())).map(DcmLoadsheetDto::getCtoNumber).collect(Collectors.toSet());
                nameCtMap.put(key, ctosTemp);
            });

            List<String> productOfferingNames = portfolioOfferingService.getAllPortfolioOfferingName();

            List<CtoCV> ctoCVS = ctoCVService.list(Wrappers.query(CtoCV.class).select("DISTINCT cto_number"));
            List<String> referenceCtoNumbers = ctoCVS.stream().map(CtoCV::getCtoNumber).toList();
            //逐行检查
            for (int i = 6; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                //#EOF之前有空行，提示错误
                if (row == null && hasEof) {
                    builder.append(CHECK_MSG_LINE).append(i + 1).append(" can not be empty, please check.\n");
                    continue;
                } else if (row == null && !hasEof) {
                    continue;
                }
                int productOfferingNameIndex = 0;
                int categoryIndex = 1;
                int productFeaturesDescriptionIndex = 2;
                int referenceCtoNumberIndex = 3;
                Cell cellProductOfferingName = row.getCell(productOfferingNameIndex);
                Cell cellCategory = row.getCell(categoryIndex);
                Cell cellProductFeaturesDescription = row.getCell(productFeaturesDescriptionIndex);
                DataFormatter formatter = new DataFormatter();
                Cell cellReferenceCtoNumberSingle = row.getCell(referenceCtoNumberIndex);
                // #EOF标识，读取文件结束
                if (cellProductOfferingName != null && CommonConstant.FLAG_END_OF_FILE.equals(formatter.formatCellValue(cellProductOfferingName))) {
                    break;
                }

                //#EOF之前有空行，提示错误
                if (hasEof && (cellProductOfferingName == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductOfferingName)))
                        && (cellCategory == null || StringUtils.isEmpty(formatter.formatCellValue(cellCategory)))
                        && (cellProductFeaturesDescription == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductFeaturesDescription)))
                        && (cellReferenceCtoNumberSingle == null || StringUtils.isEmpty(cellReferenceCtoNumberSingle.getStringCellValue()))) {
                    builder.append(CHECK_MSG_LINE).append(cellProductOfferingName.getRowIndex() + 1).append(" can not be empty, please check.\n");
                    continue;
                } else if (!hasEof && (cellProductOfferingName == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductFeaturesDescription)))
                        && (cellCategory == null || StringUtils.isEmpty(formatter.formatCellValue(cellCategory)))
                        && (cellProductFeaturesDescription == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductFeaturesDescription)))
                        && (cellReferenceCtoNumberSingle == null || StringUtils.isEmpty(cellReferenceCtoNumberSingle.getStringCellValue()))) {
                    continue;
                }

                if (cellProductOfferingName == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductOfferingName))) {
                    builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(productOfferingNameIndex + 1).append(" : Product Offering Name can not be empty, please check.\n");
                } else if (!productOfferingNames.contains(formatter.formatCellValue(cellProductOfferingName))) {
                    builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(productOfferingNameIndex + 1).append(" : Product Offering Name (").append(formatter.formatCellValue(cellProductOfferingName)).append(") is not existed, please check.\n");
                }

                if (dtoMap.get(formatter.formatCellValue(cellProductOfferingName)).size() > 1
                        || (dtoMap.get(formatter.formatCellValue(cellProductOfferingName)).size() == 1
                        && ((cellCategory != null && StringUtils.isNotEmpty(formatter.formatCellValue(cellCategory)))
                        || (cellProductFeaturesDescription != null && StringUtils.isNotEmpty(formatter.formatCellValue(cellProductFeaturesDescription)))
                )
                )
                ) {
                    if (cellCategory == null || StringUtils.isEmpty(formatter.formatCellValue(cellCategory))) {
                        builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(categoryIndex + 1).append(" : Category can not be empty, please check.\n");
                    } else if (!getOptions(sheet, 6, 1).contains(formatter.formatCellValue(cellCategory))) {
                        builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(categoryIndex + 1).append(" : Category (").append(formatter.formatCellValue(cellCategory)).append(") is not valid, please select from drop-down list.\n");
                    }

                    if (cellProductFeaturesDescription == null || StringUtils.isEmpty(formatter.formatCellValue(cellProductFeaturesDescription))) {
                        builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(productFeaturesDescriptionIndex + 1).append(" : Product Features Description can not be empty, please check.\n");
                    } else if (formatter.formatCellValue(cellProductFeaturesDescription).length() > 200) {
                        builder.append(CHECK_MSG_LINE).append(i + 1).append(CHECK_MSG_COLUMN).append(productFeaturesDescriptionIndex + 1).append(" : Product Features Description exceed 200 chars, please check.\n");
                    }
                }

                List<String> cellReferenceCtoNumber = cellProductOfferingName == null || nameCtMap.get(formatter.formatCellValue(cellProductOfferingName).trim()) == null ? null : Lists.newArrayList(nameCtMap.get(formatter.formatCellValue(cellProductOfferingName).trim()));

                if (CollectionUtils.isNotEmpty(cellReferenceCtoNumber) && cellReferenceCtoNumber.size() == 1 && StringUtils.isNotEmpty(cellReferenceCtoNumber.get(0)) && !Arrays.stream(cellReferenceCtoNumber.get(0).split(",")).map(String::trim).allMatch(referenceCtoNumbers::contains)) {
                    // cto不存在在表中
                    builder.append(CHECK_MSG_LINE).append(i + 1).append(" : Reference CTO number (").append(cellReferenceCtoNumber.get(0)).append(") is not existed, please check.\n");
                }
            }
            nameCtMap.forEach((key, value) -> {
                if (key.equals(CommonConstant.FLAG_END_OF_FILE)) {
                    return;
                }
                if (CollectionUtils.isEmpty(value)) {
                    // 没有cto
                    builder.append("Product Offering:").append(key).append(", Reference CTO number can not be empty, please check.\n");
                } else if (value.size() > 1) {
                    // 存在多个cto
                    builder.append("Product Offering:").append(key).append(", Reference CTO number (").append(StringUtils.join(value, ",")).append(") is more than 1 for same Product Offering , please check\n");
                } else if (CollectionUtils.isNotEmpty(value) && value.size() == 1 && StringUtils.isEmpty(Lists.newArrayList(value).get(0))) {
                    // 没有cto
                    builder.append("Product Offering:").append(key).append(", Reference CTO number can not be empty, please check.\n");
                }
            });

            if (!hasEof) {
                builder.append("Cannot find #EOF, please check.\n");
            }
            if (builder.length() > 0) {
                throw new DcmBusinessException(builder.toString(), CommonMsgEnum.FAIL.getCode());
            }
        } catch (IOException e) {
            log.error("Check dcm loadsheet failed:{}", e.getMessage());
            throw new DcmBusinessException("check loadsheet failed," + e.getMessage(), CommonMsgEnum.FAIL.getCode());
        }
    }

    /**
     * 找到sheet页中指定首行首列有下拉选项的单元格的可选值
     *
     * @param sheet
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    private List<String> getOptions(Sheet sheet, int rowIndex, int columnIndex) {
        List<String> options = new ArrayList<>();
        List<? extends DataValidation> dataValidations = sheet.getDataValidations();
        for (int i = 0; i < dataValidations.size(); i++) {
            DataValidation dataValidation = dataValidations.get(i);
            CellRangeAddress[] cellRangeAddress = dataValidation.getRegions().getCellRangeAddresses();
            for (int j = 0; j < cellRangeAddress.length; j++) {
                if (cellRangeAddress[j].getFirstRow() == rowIndex && cellRangeAddress[j].getFirstColumn() == columnIndex) {
                    DataValidationConstraint constraint = dataValidation.getValidationConstraint();
                    String[] optionValues = constraint.getExplicitListValues();
                    if (optionValues.length > 0) {
                        options = Lists.newArrayList(optionValues);
                    }
                    return options;
                }
            }
        }

        return options;
    }

    @Override
    @Transactional
    public List<DcmUploadResultDto> uploadDcmLoadsheet(MultipartFile file) throws IOException {
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream(), "Features");
        Cell geo = reader.getCell(1, 1);
        List<List<Object>> lists = reader.read(6);
        List<DcmLoadsheetDto> dtos = Lists.newArrayList();
        for (List<Object> obj : lists) {
            if ("#EOF".equals(String.valueOf(obj.get(0)))) {
                break;
            }
            dtos.add(DcmLoadsheetDto.builder()
                    .offeringName(obj.get(0) == null ? "" : String.valueOf(obj.get(0)))
                    .category(obj.size() <= 1 || obj.get(1) == null ? "" : String.valueOf(obj.get(1)))
                    .feature(obj.size() <= 2 || obj.get(2) == null ? "" : String.valueOf(obj.get(2)))
                    .ctoNumber(obj.size() <= 3 || obj.get(3) == null ? "" : String.valueOf(obj.get(3))).build());
        }

        Map<String, List<DcmLoadsheetDto>> dtoMap = dtos.stream().collect(Collectors.groupingBy(DcmLoadsheetDto::getOfferingName));

        precheckRole(geo.getStringCellValue());
        //文件校验
        checkLoadsheet(file, dtos, lists);

        List<DcmUploadResultDto> resultDtos = new ArrayList<>();
        Set<Long> ids = Sets.newHashSet();
        dtoMap.keySet().forEach(dtoKey -> {
            DummyConfigurableModel dcm = dummyConfigurableModelService.getDcmByNameAndGeo(dtoKey, geo.getStringCellValue());
            DummyConfigurableModelDto dummyConfigurableModelDto;
            List<DcmLoadsheetDto> dcmLoadsheetDtos = dtoMap.get(dtoKey);
            if (dcm != null) {
                DummyConfigurableModelVo checkout = checkout(dcm.getId().intValue());
                Integer newId = checkout.getId().intValue();
                DummyConfigurableModelDto modelDto = getDcmDtoById(newId);
                dummyConfigurableModelDto = handleDcmLoadsheetDto(modelDto, dcmLoadsheetDtos);
                dummyConfigurableModelDto.setVersionRemark("Updated by loadsheet");
                checkIn(dummyConfigurableModelDto);
                ids.add(newId.longValue());
            } else {
                List<PortfolioOffering> list = portfolioOfferingService.lambdaQuery().eq(PortfolioOffering::getOfferingName, dtoKey).list();
                if (dcmLoadsheetDtos.size() == 1 && StringUtils.isBlank(dcmLoadsheetDtos.get(0).getCategory()) && StringUtils.isBlank(dcmLoadsheetDtos.get(0).getFeature())) {
                    List<CtoCVsDto> cvsDtos = ctoCVService.getCVListByCtoNamesAndProductSegment(dcmLoadsheetDtos.get(0).getCtoNumber().replaceAll(" ", ""), list.get(0).getProductSegment(), geo.getStringCellValue());

                    cvsDtos.stream().collect(Collectors.groupingBy(CtoCVsDto::getCategory)).forEach((key, value) -> {
                        value.stream().map(CtoCVsDto::getValues).flatMap(List::stream).distinct().forEach(cv -> {
                            DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
                            dcmLoadsheetDto.setOfferingName(dtoKey);
                            dcmLoadsheetDto.setCategory(key);
                            dcmLoadsheetDto.setFeature(CTO_PREFIX + cv);
                            dcmLoadsheetDto.setCtoNumber(dcmLoadsheetDtos.get(0).getCtoNumber());
                            dcmLoadsheetDtos.add(dcmLoadsheetDto);
                        });
                    });
//                    cvsDtos.forEach(cvsDto -> cvsDto.getValues().forEach(value -> {
//                        DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
//                        dcmLoadsheetDto.setOfferingName(dtoKey);
//                        dcmLoadsheetDto.setCategory(cvsDto.getCategory());
//                        dcmLoadsheetDto.setFeature(CTO_PREFIX + value);
//                        dcmLoadsheetDto.setCtoNumber(dcmLoadsheetDtos.get(0).getCtoNumber());
//                        dcmLoadsheetDtos.add(dcmLoadsheetDto);
//                    }));
                    dcmLoadsheetDtos.remove(0);
                }
                DummyConfigurableModelDto modelDto = new DummyConfigurableModelDto();
                modelDto.setName(dtoKey);
                modelDto.setProductSegment(list.get(0).getProductSegment());
                modelDto.setBusinessSegment(list.get(0).getBusinessSegment());
                modelDto.setGeo(geo.getStringCellValue());
                modelDto.setReferenceCtoNumber(dcmLoadsheetDtos.get(0).getCtoNumber());
                modelDto.setDummyConfigurableModelCatagoryDtos(Lists.newArrayList());
                dummyConfigurableModelDto = handleDcmLoadsheetDto(modelDto, dcmLoadsheetDtos);
                DummyConfigurableModelDto newDcmModel = handelDataNew(dummyConfigurableModelDto, false);
                dummyConfigurableModelDto.setVersionRemark("Created by loadsheet");
                Long id = createDcm(newDcmModel);
                ids.add(id);
            }
            DcmUploadResultDto dto = new DcmUploadResultDto();
            dto.setName(dtoKey);
            dto.setGeo(geo.getStringCellValue());
            resultDtos.add(dto);
        });

        AttachmentDto attachmentDto = new AttachmentDto();
        attachmentDto.setObjectName(DummyConfigurableModel.class.getName());
        attachmentDto.setFile(file);
        attachmentDto.setObjectId(ids);
        dcmAttachmentLinkService.uploadAttachment(attachmentDto);

        return resultDtos;
    }

    private DummyConfigurableModelDto handleDcmLoadsheetDto(DummyConfigurableModelDto modelDto, List<DcmLoadsheetDto> dcmLoadsheetDtos) {
        Map<String, List<DummyConfigurableModelCatagoryDto>> categoryMap = modelDto.getDummyConfigurableModelCatagoryDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelCatagoryDto::getCategory));
        Map<String, List<DcmLoadsheetDto>> loadsheetCategoryMap = dcmLoadsheetDtos.stream().collect(Collectors.groupingBy(DcmLoadsheetDto::getCategory));
        Optional<DcmLoadsheetDto> first = dcmLoadsheetDtos.stream().filter(dcmLoadsheetDto -> StringUtils.isNotBlank(dcmLoadsheetDto.getCtoNumber())).findFirst();
        first.ifPresent(dcmLoadsheetDto -> modelDto.setReferenceCtoNumber(dcmLoadsheetDto.getCtoNumber().replaceAll("", "")));
        List<DummyConfigurableModelCatagoryDto> categories = loadsheetCategoryMap.keySet().stream().map(category -> {
            List<DummyConfigurableModelCatagoryDto> dummyConfigurableModelCatagoryDtos = categoryMap.get(category);
            DummyConfigurableModelCatagoryDto originCategoryDto = null;
            Map<String, List<DummyConfigurableModelFeatureDto>> originFeatureMap = Maps.newHashMap();
            if (CollectionUtil.isNotEmpty(dummyConfigurableModelCatagoryDtos)) {
                originCategoryDto = dummyConfigurableModelCatagoryDtos.get(0);
                originFeatureMap = originCategoryDto.getDummyConfigurableModelFeatureDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelFeatureDto::getDescName));
            }
            DummyConfigurableModelCatagoryDto dummyConfigurableModelCatagoryDto = new DummyConfigurableModelCatagoryDto();
            dummyConfigurableModelCatagoryDto.setId(originCategoryDto == null ? null : originCategoryDto.getId());
            dummyConfigurableModelCatagoryDto.setCategory(category);
            dummyConfigurableModelCatagoryDto.setFullCvs(originCategoryDto == null ? null : originCategoryDto.getFullCvs());
            Map<String, List<DummyConfigurableModelFeatureDto>> finalOriginFeatureMap = originFeatureMap;
            dummyConfigurableModelCatagoryDto.setDummyConfigurableModelFeatureDtos(loadsheetCategoryMap.get(category).stream().map(featureDto -> {
                DummyConfigurableModelFeatureDto dummyConfigurableModelFeatureDto = new DummyConfigurableModelFeatureDto();
                List<DummyConfigurableModelFeatureDto> dummyConfigurableModelFeatureDtos = finalOriginFeatureMap.get(featureDto.getFeature());
                DummyConfigurableModelFeatureDto modelFeatureDto = null;
                if (CollectionUtil.isNotEmpty(dummyConfigurableModelFeatureDtos)) {
                    modelFeatureDto = dummyConfigurableModelFeatureDtos.get(0);
                }
                dummyConfigurableModelFeatureDto.setId(modelFeatureDto == null ? null : modelFeatureDto.getId());
                dummyConfigurableModelFeatureDto.setDescName(featureDto.getFeature());
                dummyConfigurableModelFeatureDto.setFinalColor(modelFeatureDto == null ? null : modelFeatureDto.getFinalColor());
                dummyConfigurableModelFeatureDto.setFinalCvs(modelFeatureDto == null ? null : modelFeatureDto.getFinalCvs());
                dummyConfigurableModelFeatureDto.setGvColor(modelFeatureDto == null ? null : modelFeatureDto.getGvColor());
                dummyConfigurableModelFeatureDto.setGvItemId(modelFeatureDto == null ? null : modelFeatureDto.getGvItemId());
                dummyConfigurableModelFeatureDto.setRecommendedGv(modelFeatureDto == null ? null : modelFeatureDto.getRecommendedGv());
                dummyConfigurableModelFeatureDto.setReferenceCtoCv(modelFeatureDto == null ? null : modelFeatureDto.getReferenceCtoCv());
                dummyConfigurableModelFeatureDto.setSaved(modelFeatureDto == null ? null : modelFeatureDto.getSaved());
                return dummyConfigurableModelFeatureDto;
            }).toList());
            return dummyConfigurableModelCatagoryDto;
        }).toList();
        modelDto.setDummyConfigurableModelCatagoryDtos(categories);
        return modelDto;
    }

//    private DummyConfigurableModelDto handleDcmLoadsheetDto(DummyConfigurableModelDto modelDto, List<DcmLoadsheetDto> dcmLoadsheetDtos) {
//        Map<String, List<DummyConfigurableModelCatagoryDto>> categoryMap = modelDto.getDummyConfigurableModelCatagoryDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelCatagoryDto::getCategory));
//        return dcmLoadsheetDtos.stream().map(dto -> {
//            DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
//            BeanUtil.copyProperties(dto, dcmLoadsheetDto);
//            String category = dto.getCategory();
//            String feature = dto.getFeature();
//            List<DummyConfigurableModelCatagoryDto> dummyConfigurableModelCatagoryDtos = categoryMap.get(category);
//            if (CollectionUtil.isNotEmpty(dummyConfigurableModelCatagoryDtos)) {
//                DummyConfigurableModelCatagoryDto dummyConfigurableModelCatagoryDto = dummyConfigurableModelCatagoryDtos.get(0);
//                Map<String, List<DummyConfigurableModelFeatureDto>> featureMap = dummyConfigurableModelCatagoryDto.getDummyConfigurableModelFeatureDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelFeatureDto::getDescName));
//                List<DummyConfigurableModelFeatureDto> dummyConfigurableModelFeatureDtos = featureMap.get(feature);
//                List<DummyConfigurableModelFeatureDto> dtos = dummyConfigurableModelFeatureDtos.stream().filter(dummyConfigurableModelFeatureDto -> dummyConfigurableModelFeatureDto.getDescName().equals(dto.getFeature())).toList();
//                if (CollectionUtil.isNotEmpty(dtos)) {
//                    DummyConfigurableModelFeatureDto dummyConfigurableModelFeatureDto = dtos.get(0);
//                    dcmLoadsheetDto.setDcmId(modelDto.getId());
//                    dcmLoadsheetDto.setCategoryId(dummyConfigurableModelCatagoryDto.getId());
//                    dcmLoadsheetDto.setFeatureId(dummyConfigurableModelFeatureDto.getId());
//                }
//            }
//            return dcmLoadsheetDto;
//        }).toList();
//    }

    private void recoverDummyConfigurableModelDtoId(DummyConfigurableModelDto sourceDummyConfigurableModelDto, DummyConfigurableModelDto targetDummyConfigurableModelDto) {
        targetDummyConfigurableModelDto.setId(sourceDummyConfigurableModelDto.getId());
        Map<String, List<DummyConfigurableModelCatagoryDto>> sourceDummyConfigurableModelDtoMap = sourceDummyConfigurableModelDto.getDummyConfigurableModelCatagoryDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelCatagoryDto::getCategory));
        // 给category赋值id
        targetDummyConfigurableModelDto.getDummyConfigurableModelCatagoryDtos().forEach(dummyConfigurableModelCatagoryDto -> {
            List<DummyConfigurableModelCatagoryDto> dummyConfigurableModelCatagoryDtos = sourceDummyConfigurableModelDtoMap.get(dummyConfigurableModelCatagoryDto.getCategory());
            if (CollectionUtil.isNotEmpty(dummyConfigurableModelCatagoryDtos)) {
                DummyConfigurableModelCatagoryDto sourceDummyConfigurableModelCatagoryDto = dummyConfigurableModelCatagoryDtos.get(0);
                dummyConfigurableModelCatagoryDto.setId(sourceDummyConfigurableModelCatagoryDto.getId());
                // 给feature赋值id
                Map<String, List<DummyConfigurableModelFeatureDto>> sourceDummyConfigurableModelFeatureDtoMap = sourceDummyConfigurableModelCatagoryDto.getDummyConfigurableModelFeatureDtos().stream().collect(Collectors.groupingBy(DummyConfigurableModelFeatureDto::getDescName));
                dummyConfigurableModelCatagoryDto.getDummyConfigurableModelFeatureDtos().forEach(dummyConfigurableModelFeatureDto -> {
                    List<DummyConfigurableModelFeatureDto> dummyConfigurableModelFeatureDtos = sourceDummyConfigurableModelFeatureDtoMap.get(dummyConfigurableModelFeatureDto.getDescName());
                    if (CollectionUtil.isNotEmpty(dummyConfigurableModelFeatureDtos)) {
                        DummyConfigurableModelFeatureDto sourceDummyConfigurableModelFeatureDto = dummyConfigurableModelFeatureDtos.get(0);
                        dummyConfigurableModelFeatureDto.setId(sourceDummyConfigurableModelFeatureDto.getId());
                    }
                });
            }
        });
    }

    @Override
    @Transactional
    public List<Long> refreshByIds(String ids) {
        String[] split = ids.split(",");
        List<Long> list = Arrays.stream(split).map(Long::valueOf).toList();
        List<DummyConfigurableModel> dummyConfigurableModels = dummyConfigurableModelService.lambdaQuery().in(DummyConfigurableModel::getId, list).list();
        precheck(list, dummyConfigurableModels);
        List<Long> returnIds = Lists.newArrayList();
        dummyConfigurableModels.forEach(dummyConfigurableModel -> {
            // 旧的dcm对象checkout
            DummyConfigurableModelVo checkout = checkout(dummyConfigurableModel.getId().intValue());
            dummyConfigurableModelService.update(Wrappers.lambdaUpdate(DummyConfigurableModel.class)
                    .set(DummyConfigurableModel::getState, DcmConstant.DCM_STATUS_CHANGED)
                    .eq(DummyConfigurableModel::getId, checkout.getId()));
            Integer newId = checkout.getId().intValue();
            returnIds.add(newId.longValue());
            DummyConfigurableModelDto dummyConfigurableModelDto = getDcmDtoById(newId);
            dummyConfigurableModelDto.setVersionRemark("Updated by Refresh action");
            dummyConfigurableModelDto.getDummyConfigurableModelCatagoryDtos().forEach(dummyConfigurableModelCatagoryDto -> {
                dummyConfigurableModelCatagoryDto.getDummyConfigurableModelFeatureDtos().forEach(dummyConfigurableModelFeatureDto -> {
                    if (!DcmConstant.DCM_FEATURE_COLOR_BLACK.equals(dummyConfigurableModelFeatureDto.getFinalColor())) {
                        dummyConfigurableModelFeatureDto.setFinalCvs(null);
                    }
                });
            });
            DummyConfigurableModelDto resultDto = handelDataNew(dummyConfigurableModelDto, true);
            System.out.println(resultDto);
            dummyConfigurableModelService.saveLinkNew(resultDto);
            // 保存本身, 并变更状态
            dummyConfigurableModelService.checkIn(dummyConfigurableModelDto);
        });
        return returnIds;
    }

    private void precheck(List<Long> ids, List<DummyConfigurableModel> models) {
        if (ids.size() != models.size()) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        List<DummyConfigurableModel> notLastest = models.stream().filter(model -> !model.getLastestAll()).toList();
        if (CollectionUtil.isNotEmpty(notLastest)) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_IS_NOT_LASTEST);
        }
        List<Long> undoCheckoutIds = Lists.newArrayList();
        for (DummyConfigurableModel model : models) {
            if (StringUtils.isNotBlank(model.getCheckoutBy())) {
                undoCheckoutIds.add(model.getMasterId());
            }
        }
        if (CollectionUtil.isNotEmpty(undoCheckoutIds)) {
            StringBuffer notice = new StringBuffer();
            dummyConfigurableModelMasterService.list(Wrappers.lambdaQuery(DummyConfigurableModelMaster.class).in(DummyConfigurableModelMaster::getId, undoCheckoutIds))
                    .forEach(dummyConfigurableModelMaster -> {
                        models.stream().filter(model -> model.getMasterId().equals(dummyConfigurableModelMaster.getId())).findFirst().ifPresent(model -> {
                            if (ContextUtil.getCurrentUser().equals(model.getCheckoutBy())) {
                                notice.append("DCM ").append(dummyConfigurableModelMaster.getNumber()).append(" has been checked out by you, please undo checkout or save your change.<br />");
                            } else {
                                notice.append("DCM ").append(dummyConfigurableModelMaster.getNumber()).append(" has been checked out by ").append(model.getCheckoutBy()).append(".<br />");
                            }
                        });
                    });
            throw new DcmBusinessException(notice.toString(), BusinessMsgEnum.DCM_IS_NOT_LASTEST.getCode());
        }
    }

    private DummyConfigurableModelDto getDcmDtoById(Integer id) {
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        return dummyConfigurableModelService.getDcmDtoById(dummyConfigurableModel);
    }

    @Override
    @Transactional
    public Long copyById(Integer id, String portfolioName, String geo) {
        List<PortfolioOffering> list = portfolioOfferingService.lambdaQuery().eq(PortfolioOffering::getOfferingName, portfolioName).list();
        if (CollectionUtil.isEmpty(list)) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_OFFERING_NAME_NOT_EXIST);
        }
        DummyConfigurableModel dcm = dummyConfigurableModelService.getDcmByNameAndGeo(portfolioName, geo);
        if (dcm != null) {
            throw new DcmBusinessException(BusinessMsgEnum.OFFERING_NAME_AND_GEO_HAS_EXISTED);
        }
        DummyConfigurableModelDto dummyConfigurableModelDto = getDcmDtoById(id);
        dummyConfigurableModelDto.setName(portfolioName);
        dummyConfigurableModelDto.setGeo(geo);
        dummyConfigurableModelDto.setVersionRemark("Created by Copy action");
        return createDcm(dummyConfigurableModelDto);
    }

    @Override
    public DummyConfigurableModelVo getLastVersionById(Integer id) {
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        List<DummyConfigurableModel> dummyConfigurableModels = dummyConfigurableModelService.lambdaQuery()
                .eq(DummyConfigurableModel::getMasterId, dummyConfigurableModel.getMasterId())
                .eq(DummyConfigurableModel::getLastestAll, true)
                .eq(DummyConfigurableModel::getDeleted, 0).list();
        if (CollectionUtil.isEmpty(dummyConfigurableModels)) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        return dummyConfigurableModelService.getDcmById(dummyConfigurableModels.get(0));
    }

    @Override
    public List<DcmFullCvVo> getFullCvs(Integer id, String ctoNumber) {
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        if (StringUtils.isBlank(ctoNumber)) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_CTO_NUMBER_NOT_EXIST);
        }
//        boolean exists = ctoCVService.exists(Wrappers.lambdaQuery(CtoCV.class).eq(CtoCV::getCtoNumber, ctoNumber));
//        if (!exists) {
//            throw new DcmBusinessException(BusinessMsgEnum.DCM_CTO_NUMBER_NOT_EXIST);
//        }
        LambdaQueryWrapper<DummyConfigurableModelCategory> lambdaQueryWrapper = Wrappers.lambdaQuery(DummyConfigurableModelCategory.class);
        lambdaQueryWrapper.eq(DummyConfigurableModelCategory::getDcmId, id);
        List<DummyConfigurableModelCategory> categories = dummyConfigurableModelCategoryService.list(lambdaQueryWrapper);
        List<DcmFullCvVo> result = Lists.newArrayList();
        categories.forEach(category -> {
            if (ObjectUtil.isNotNull(category.getFullCv())) {
                List<CtoCVsDto> ctoCVsDtos = JSONUtil.toList(JSONUtil.toJsonStr(category.getFullCv()), CtoCVsDto.class);
                result.addAll(
                        ctoCVsDtos.stream()
                                .filter(item -> item.getCtoNumber().equals(ctoNumber))
                                .collect(Collectors.toMap(CtoCVsDto::getCategory, a -> a, (v1, v2) -> {
                                    v1.setCtoNumber(v1.getCtoNumber());
                                    if (Arrays.stream(v1.getCharname().split(",")).noneMatch(item -> item.equals(v2.getCharname()))) {
                                        v1.setCharname(v1.getCharname() + "," + v2.getCharname());
                                    }
                                    v1.getValues().addAll(v2.getValues());
                                    return v1;
                                })).values().stream()
                                .map(ctoCVsDto -> {
                                    DcmFullCvVo dcmFullCvVo = new DcmFullCvVo();
                                    dcmFullCvVo.setCategory(ctoCVsDto.getCategory());
                                    dcmFullCvVo.setCharacteristics(ctoCVsDto.getCharname());
                                    dcmFullCvVo.setFullCvs(StrUtil.join(",", ctoCVsDto.getValues().stream().distinct().toList()));
                                    return dcmFullCvVo;
                                }).toList());
            }
        });
        return result;
    }

    // 处理的核心逻辑
    private List<DummyConfigurableModelDto> handelData(List<DcmLoadsheetDto> dtos) {
        List<DummyConfigurableModelDto> dummyConfigurableModelDtos = Lists.newArrayList();
        // 根据offering分组, 分别创建DTO对象
        Map<String, List<DcmLoadsheetDto>> dcms = dtos.stream().collect(Collectors.groupingBy(DcmLoadsheetDto::getOfferingName));
        List<String> offeringNames = dtos.stream().map(DcmLoadsheetDto::getOfferingName).toList();
        List<PortfolioOffering> portfolioOfferings = portfolioOfferingService.lambdaQuery().eq(PortfolioOffering::getOfferingName, offeringNames.get(0)).list();
        List<String> verifyOfferings = portfolioOfferings.stream().map(PortfolioOffering::getOfferingName).toList();
        Optional<String> verifyOfferingsOptional = offeringNames.stream().filter(offeringName -> !verifyOfferings.contains(offeringName)).findAny();
        // 验证 列表中的offering name 在列表中存在
        if (verifyOfferingsOptional.isPresent()) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_OFFERING_NAME_NOT_EXIST);
        }
        // 获取全部的初始化数据 begin
        List<Gv> list = gvService.lambdaQuery().in(Gv::getStatus, Lists.newArrayList("New", "Released")).list();
        List<DcmBusinessConfigurationKeywords> keywards = dcmBusinessConfigurationKeywordsService.getAll();
        List<DcmBusinessConfigurationCategoryC> categoryCs = dcmBusinessConfigurationCategoryCService.getAll();
        Map<String, List<PortfolioOffering>> offeringMap = portfolioOfferings.stream().collect(Collectors.groupingBy(PortfolioOffering::getOfferingName));
        // 获取全部的初始化数据 end
        dcms.keySet().forEach(offeringName -> {
            // 获取原有的DTO
            // 创建DummyConfigurableModelDto对象
            DummyConfigurableModelDto dummyConfigurableModelDto = new DummyConfigurableModelDto();

            dummyConfigurableModelDto.setName(offeringName);
            // 获取同offering下的所有dcmloadsheetdto对象
            List<DcmLoadsheetDto> dcmLoadsheetDtos = dcms.get(offeringName);
            dummyConfigurableModelDto.setId(dcmLoadsheetDtos.get(0).getDcmId());
            PortfolioOffering portfolioOffering = offeringMap.get(offeringName).get(0);
            dummyConfigurableModelDto.setProductSegment(portfolioOffering.getProductSegment());
            dummyConfigurableModelDto.setBusinessSegment(portfolioOffering.getBusinessSegment());
            // 需要存在对应的CTO
            Optional<DcmLoadsheetDto> ctoDcm = dcmLoadsheetDtos.stream().filter(dcmLoadsheetDto -> StringUtils.isNotBlank(dcmLoadsheetDto.getCtoNumber())).findFirst();
            if (ctoDcm.isPresent()) {
                String ctoNumber = ctoDcm.get().getCtoNumber();
                dummyConfigurableModelDto.setReferenceCtoNumber(ctoNumber);
                List<CtoCVsDto> cvsDtos = ctoCVService.getCVsByCtoName(ctoNumber);
                List<Gv> gvs = new ArrayList<>(list.stream()
                        .filter(gv -> portfolioOffering.getProductSegment().equals(gv.getProductSegment())
                                && ("SMB".equals(portfolioOffering.getBusinessSegment()) || portfolioOffering.getBusinessSegment().equals(gv.getBusinessSegment()))).toList());
//                List<CtoCVsDto> cvsDtos = getCVSByCtoNumber(ctoNumber);
                // 根据category进行分组, 算出每个category 对应的 cv值
                Map<String, List<DcmLoadsheetDto>> categories = dcmLoadsheetDtos.stream().collect(Collectors.groupingBy(DcmLoadsheetDto::getCategory));
                List<DummyConfigurableModelCatagoryDto> dummyConfigurableModelCatagoryDtos = Lists.newArrayList();
                categories.keySet().forEach(category -> {
                    DummyConfigurableModelCatagoryDto dummyConfigurableModelCatagoryDto = new DummyConfigurableModelCatagoryDto();
                    List<DcmLoadsheetDto> categoryDcm = categories.get(category);
                    dummyConfigurableModelCatagoryDto.setId(categoryDcm.get(0).getCategoryId());
                    dummyConfigurableModelCatagoryDto.setCategory(category);
                    // 根据category得出可取的cv值 并计算出对应的features
                    List<String> cvs = getCVSByCatagory(cvsDtos, categoryCs, portfolioOffering.getProductSegment(), category);
//                    dummyConfigurableModelCatagoryDto.setFullCvs(cvs);
                    // 计算features
                    List<DummyConfigurableModelFeatureDto> dummyConfigurableModelFeatureDtos = categoryDcm.stream().map(dcmLoadsheetDto -> {
                        DummyConfigurableModelFeatureDto dummyConfigurableModelFeatureDto = new DummyConfigurableModelFeatureDto();
                        dummyConfigurableModelFeatureDto.setId(dcmLoadsheetDto.getFeatureId());
                        String feature = dcmLoadsheetDto.getFeature();
                        dummyConfigurableModelFeatureDto.setDescName(feature);
                        // 替换keywords
                        List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords = commonKeyWordFilter(category, portfolioOffering.getProductSegment(), portfolioOffering.getBusinessSegment(), keywards);
                        if (CollectionUtil.isEmpty(dcmBusinessConfigurationKeywords)) {
                            dcmBusinessConfigurationKeywords = Lists.newArrayList();
                        }
                        List<Gv> matchGvs = Lists.newArrayList();
                        List<String> matchCvs = Lists.newArrayList();
                        if (feature.startsWith(CTO_PREFIX)) {
                            matchCvs.add(feature.substring(11));
                        } else {
                            String gvFeature = StringUtils.upperCase(feature);
                            String cvFeature = StringUtils.upperCase(feature);
                            dcmBusinessConfigurationKeywords = dcmBusinessConfigurationKeywords.stream().peek(keyword -> {
                                keyword.setOriginalValue(StringUtils.upperCase(keyword.getOriginalValue()));
                                keyword.setCvReplacementValue(StringUtils.upperCase(keyword.getCvReplacementValue()));
                                keyword.setGvReplacementValue(StringUtils.upperCase(keyword.getGvReplacementValue()));
                            }).toList();
                            List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(dcmBusinessConfigurationKeyword -> StringUtils.upperCase(feature).contains(dcmBusinessConfigurationKeyword.getOriginalValue())).toList();
                            if (CollectionUtil.isNotEmpty(keywords)) {
                                for (DcmBusinessConfigurationKeywords keyword : keywords) {
                                    gvFeature = gvFeature.replace(keyword.getOriginalValue(), keyword.getGvReplacementValue());
                                    cvFeature = cvFeature.replace(keyword.getOriginalValue(), keyword.getCvReplacementValue());
                                }
                            }
                            List<String> gvOriginFeatureKeyWords = new ArrayList<>(Arrays.stream(gvFeature.split("_| ")).toList());
                            List<String> cvOriginFeatureKeyWords = new ArrayList<>(Arrays.stream(cvFeature.split("_| ")).toList());
                            matchGvs = getMatchGvs(category, portfolioOffering.getProductSegment(), portfolioOffering.getBusinessSegment(), gvOriginFeatureKeyWords, gvs, keywords);
                            matchCvs = getMatchCvs(category, portfolioOffering.getProductSegment(), portfolioOffering.getBusinessSegment(), cvOriginFeatureKeyWords, cvs, keywords);
                        }
                        String cv = CollectionUtil.join(matchCvs, ",");
                        List<String> gvStrings = matchGvs.stream().map(Gv::getGlobalValue).toList();
                        String gv = CollectionUtil.join(gvStrings, ",");
                        String gvColorString = "";
                        List<String> existGvs = Lists.newArrayList();
                        if (CollectionUtil.isNotEmpty(gvStrings)) {
                            gvStrings.forEach(gvString -> {
                                List<CtoCV> ctoCVS = ctoCVService.lambdaQuery().like(CtoCV::getValue, gvString).list();
                                Optional<CtoCV> cvOptional = ctoCVS.stream().filter(ctoCV -> Arrays.asList(ctoCV.getValue().split(",")).contains(gvString)).findAny();
                                if (cvOptional.isPresent()) {
                                    existGvs.add(gvString);
                                }
                            });
                            gvColorString = gvStrings.stream().map(gvValue -> existGvs.contains(gvValue) ? DcmConstant.DCM_FEATURE_COLOR_BLACK : DcmConstant.DCM_FEATURE_COLOR_ORANGE).collect(Collectors.joining(","));
                        }
                        dummyConfigurableModelFeatureDto.setReferenceCtoCv(cv);
                        dummyConfigurableModelFeatureDto.setRecommendedGv(gv);
                        dummyConfigurableModelFeatureDto.setCvColor(null);
                        dummyConfigurableModelFeatureDto.setGvColor(gvColorString);
                        getMatchfinalValueAndColor(matchGvs, matchCvs, dummyConfigurableModelFeatureDto, feature, existGvs);
                        // 根据最终值和gv值的比较 看是否有gvitemid
                        if (StringUtils.isNotBlank(dummyConfigurableModelFeatureDto.getFinalCvs())
                                && dummyConfigurableModelFeatureDto.getFinalCvs().equals(gv)) {
                            dummyConfigurableModelFeatureDto.setGvItemId("gvItemId");
                        }
                        return dummyConfigurableModelFeatureDto;
                    }).toList();
                    dummyConfigurableModelCatagoryDto.setDummyConfigurableModelFeatureDtos(dummyConfigurableModelFeatureDtos);
                    dummyConfigurableModelCatagoryDtos.add(dummyConfigurableModelCatagoryDto);
                });
                dummyConfigurableModelDto.setDummyConfigurableModelCatagoryDtos(dummyConfigurableModelCatagoryDtos);
            }
            dummyConfigurableModelDtos.add(dummyConfigurableModelDto);
        });
        return dummyConfigurableModelDtos;
    }

    private List<String> getMatchCvs(String category, String productSegment, String businessSegment, List<String> originFeatureKeyWords, List<String> cvs, List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords) {
        // 1. 查询出小范围的keywords
//        List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords = commonKeyWordFilter(category, productSegment, businessSegment, keywards);
//        if (dcmBusinessConfigurationKeywords == null) {
//            dcmBusinessConfigurationKeywords = Lists.newArrayList();
//        }

        // 2. 循环feature每个词
        List<String> returnCvs = Lists.newArrayList(cvs);
        for (String featureWord : originFeatureKeyWords) {
            List<String> retainCvs = Lists.newArrayList();
            if (featureWord.contains("+")) {
                List<String> splitFeatureWords = Lists.newArrayList(Arrays.stream(featureWord.split("\\+")).toList());
                splitFeatureWords.add(featureWord);
                for (String splitFeatureWord : splitFeatureWords) {
                    List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> keyword.getCvReplacementValue().equals(splitFeatureWord)).toList();
                    retainCvs.addAll(matchCv(returnCvs, keywords, splitFeatureWord));
                    CollectionUtil.distinct(retainCvs);
                }
            } else {
                List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> keyword.getCvReplacementValue().equals(featureWord)).toList();
                retainCvs = matchCv(returnCvs, keywords, featureWord);
            }

//            List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> keyword.getOriginalValue().equals(featureWord)).toList();
//            if (CollectionUtil.isEmpty(keywords)) { // 如果小范围的keywords 为空 或者 未匹配到关键字 则用原词 精准匹配
//                retainCvs = returnCvs.stream().filter(cv -> Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).toList().contains(StringUtils.lowerCase(featureWord))).toList();
//            } else { // 如果匹配到keywords 则看匹配到的按匹配类型 根据替换词 查找结果
//                DcmBusinessConfigurationKeywords dcmBusinessConfigurationKeywords1 = keywords.get(0);
//                if ("Exact".equals(dcmBusinessConfigurationKeywords1.getMatchType())) {
//                    retainCvs = returnCvs.stream().filter(cv -> Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).toList().contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getCvReplacementValue()))).toList();
//                } else {
//                    retainCvs = returnCvs.stream().filter(cv ->
//                            Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).anyMatch(splitCv -> splitCv.contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getCvReplacementValue())))).toList();
//                }
//            }
            returnCvs.retainAll(retainCvs);
        }
        return returnCvs;
    }

    private List<String> matchCv(List<String> returnCvs, List<DcmBusinessConfigurationKeywords> keywords, String featureWord) {
        if (CollectionUtil.isEmpty(keywords)) { // 如果小范围的keywords 为空 或者 未匹配到关键字 则用原词 精准匹配
            return returnCvs.stream().filter(cv -> Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).toList().contains(StringUtils.lowerCase(featureWord))).toList();
        } else { // 如果匹配到keywords 则看匹配到的按匹配类型 根据替换词 查找结果
            DcmBusinessConfigurationKeywords dcmBusinessConfigurationKeywords = keywords.get(0);
            if ("Exact".equals(dcmBusinessConfigurationKeywords.getMatchType())) {
                return returnCvs.stream().filter(cv -> Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).toList().contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords.getCvReplacementValue()))).toList();
            } else {
                return returnCvs.stream().filter(cv ->
                        Arrays.stream(StringUtils.lowerCase(cv).split(" |_")).anyMatch(splitCv -> splitCv.contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords.getCvReplacementValue())))).toList();
            }
        }
    }

    private List<Gv> getMatchGvs(String category, String productSegment, String businessSegment, List<String> originFeatureKeyWords, List<Gv> gvs, List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords) {
//        // 1. 查询出小范围的keywords
//        List<DcmBusinessConfigurationKeywords> dcmBusinessConfigurationKeywords = commonKeyWordFilter(category, productSegment, businessSegment, keywards);
//        if (dcmBusinessConfigurationKeywords == null) {
//            dcmBusinessConfigurationKeywords = Lists.newArrayList();
//        }

        // 2. 循环feature每个词
        List<Gv> returnGvs = Lists.newArrayList(gvs);
        for (String featureWord : originFeatureKeyWords) {
            List<Gv> retainGvs = Lists.newArrayList();
            if (featureWord.contains("+")) {
                List<String> splitFeatureWords = new ArrayList<>(Arrays.stream(featureWord.split("\\+")).toList());
                splitFeatureWords.add(featureWord);
                for (String splitFeatureWord : splitFeatureWords) {
                    List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> keyword.getGvReplacementValue().equals(splitFeatureWord)).toList();
                    retainGvs.addAll(matchGv(returnGvs, keywords, splitFeatureWord));
                    CollectionUtil.distinct(retainGvs);
//                    CollectionUtil.union(retainGvs, matchGv(returnGvs, keywords, splitFeatureWord));
                }
            } else {
                List<DcmBusinessConfigurationKeywords> keywords = dcmBusinessConfigurationKeywords.stream().filter(keyword -> keyword.getGvReplacementValue().equals(featureWord)).toList();
                retainGvs = matchGv(returnGvs, keywords, featureWord);
//                if (CollectionUtil.isEmpty(keywords)) { // 如果小范围的keywords 为空 或者 未匹配到关键字 则用原词 精准匹配
//                    retainGvs = returnGvs.stream().filter(gv -> Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).toList().contains(StringUtils.lowerCase(featureWord))).toList();
//                } else { // 如果匹配到keywords 则看匹配到的按匹配类型 根据替换词 查找结果
//                    DcmBusinessConfigurationKeywords dcmBusinessConfigurationKeywords1 = keywords.get(0);
//                    if ("Exact".equals(dcmBusinessConfigurationKeywords1.getMatchType())) {
//                        retainGvs = returnGvs.stream().filter(gv -> Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).toList().contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getGvReplacementValue()))).toList();
//                    } else {
//                        retainGvs = returnGvs.stream().filter(gv ->
//                                Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).anyMatch(splitCv -> splitCv.contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getGvReplacementValue())))).toList();
//                    }
//                }
            }
            returnGvs.retainAll(retainGvs);
        }

        return returnGvs.stream()
                .collect(Collectors.collectingAndThen(Collectors.toCollection(
                        () -> new TreeSet<>(Comparator.comparing(Gv::getGlobalValue))), ArrayList::new));
    }

    private List<Gv> matchGv(List<Gv> returnGvs, List<DcmBusinessConfigurationKeywords> keywords, String featureWord) {
        if (CollectionUtil.isEmpty(keywords)) { // 如果小范围的keywords 为空 或者 未匹配到关键字 则用原词 精准匹配
            return returnGvs.stream().filter(gv -> Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).toList().contains(StringUtils.lowerCase(featureWord))).toList();
        } else { // 如果匹配到keywords 则看匹配到的按匹配类型 根据替换词 查找结果
            DcmBusinessConfigurationKeywords dcmBusinessConfigurationKeywords1 = keywords.get(0);
            if ("Exact".equals(dcmBusinessConfigurationKeywords1.getMatchType())) {
                return returnGvs.stream().filter(gv -> Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).toList().contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getGvReplacementValue()))).toList();
            } else {
                return returnGvs.stream().filter(gv ->
                        Arrays.stream(StringUtils.lowerCase(gv.getGlobalValue()).split(" |_")).anyMatch(splitCv -> splitCv.contains(StringUtils.lowerCase(dcmBusinessConfigurationKeywords1.getGvReplacementValue())))).toList();
            }
        }
    }

    private List<DcmBusinessConfigurationKeywords> commonKeyWordFilter(String category, String productSegment, String businessSegment, List<DcmBusinessConfigurationKeywords> keywards) {
        return keywards.stream().filter(keyward -> category.equals(keyward.getCategory()))
                .filter(keyward -> keyward.getBusinessSegment() == null || "SMB".equals(businessSegment) || keyward.getBusinessSegment().equals(businessSegment))
                .filter(keyward -> keyward.getProductSegment() == null || keyward.getProductSegment().equals(productSegment)).toList();
    }


    private void getMatchfinalValueAndColor(List<Gv> matchGvs, List<String> matchCvs, DummyConfigurableModelFeatureDto dto, String desc, List<String> existGvs) {
        if (matchGvs.size() == 1) {
            if (existGvs.contains(matchGvs.get(0).getGlobalValue())) {
                dto.setFinalCvs(matchGvs.get(0).getGlobalValue());
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
            } else {
                if (matchCvs.size() == 1) {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                } else if (matchCvs.size() == 0) {
                    dto.setFinalCvs(matchGvs.get(0).getGlobalValue());
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
                } else {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            }
        } else if (matchGvs.size() == 0) {
            if (matchCvs.size() == 1) {
                dto.setFinalCvs(matchCvs.get(0));
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
            } else if (matchCvs.size() == 0) {
                dto.setFinalCvs(desc);
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
            } else {
                dto.setFinalCvs(matchCvs.get(0));
                dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
            }
        } else {
            List<Gv> gvs = matchGvs.stream().filter(gv -> existGvs.contains(gv.getGlobalValue())).toList();
            if (CollectionUtil.isEmpty(gvs)) {
                if (matchCvs.size() == 1) {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                } else if (matchCvs.size() == 0) {
                    dto.setFinalCvs(matchGvs.get(0).getGlobalValue());
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_ORANGE);
                } else {
                    dto.setFinalCvs(matchCvs.get(0));
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            } else {
                if (matchCvs.size() > 0) {
                    List<Gv> commonGvs = gvs.stream().filter(gv -> matchCvs.contains(gv.getGlobalValue())).toList();
                    if (commonGvs.size() == 1) {
                        dto.setFinalCvs(commonGvs.get(0).getGlobalValue());
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_GREEN);
                    } else if (commonGvs.size() > 1) {
                        dto.setFinalCvs(commonGvs.get(0).getGlobalValue());
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                    } else {
                        dto.setFinalCvs(matchGvs.get(0).getGlobalValue());
                        dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                    }
                } else {
                    dto.setFinalCvs(gvs.get(0).getGlobalValue());
                    dto.setFinalColor(DcmConstant.DCM_FEATURE_COLOR_RED);
                }
            }
        }
    }


    private List<Gv> getMatchGvList(String productSegment, String businessSegment, List<Gv> list, String replacementGv) {
        return list.stream().filter(gv -> productSegment.equals(gv.getProductSegment())).filter(gv -> businessSegment.equals(gv.getBusinessSegment())).filter(gv -> gv.getGlobalDescription().contains(replacementGv)).toList();
    }

    private List<String> getMatchCvList(List<String> cvs, String replacementCv) {
        return cvs.stream().filter(cv -> cv.contains(replacementCv)).toList();
    }

    private DcmBusinessConfigurationKeywords replacementKeywords(String productSegment, String businessSegment, String category, String feature, List<DcmBusinessConfigurationKeywords> keywards) {
        Optional<DcmBusinessConfigurationKeywords> configurationKeywards = keywards.stream().filter(keyward -> keyward.getCategory().equals(category)).filter(keyward -> keyward.getBusinessSegment() == null || keyward.getBusinessSegment().equals(businessSegment)).filter(keyward -> keyward.getProductSegment() == null || keyward.getProductSegment().equals(productSegment)).filter(keyward -> "Exact".equals(keyward.getMatchType()) && keyward.getOriginalValue().equals(feature) || "Fuzzy".equals(keyward.getMatchType()) && feature.contains(keyward.getOriginalValue())).findFirst();
        return configurationKeywards.orElse(null);
    }

    private List<String> getCVSByCatagory(List<CtoCVsDto> cvsDtos, List<DcmBusinessConfigurationCategoryC> categoryCs, String productSegment, String category) {
        List<String> characteristics = categoryCs.stream().filter(categoryc -> categoryc.getCategory() == null || categoryc.getCategory().equals(category)).filter(categoryc -> categoryc.getNbDt() == null || categoryc.getNbDt().equals(productSegment)).map(categoryC -> Arrays.stream(categoryC.getCharacteristic().split(",")).toList()).flatMap(List::stream).toList();
        List<List<String>> lists = cvsDtos.stream().filter(ctoCVsDto -> characteristics.contains(ctoCVsDto.getCategory())).map(CtoCVsDto::getValues).toList();
        List<String> allCvs = Lists.newArrayList();
        lists.forEach(allCvs::addAll);
        List<String> returnCvs = Lists.newArrayList();
        allCvs.forEach(cv -> returnCvs.addAll(Arrays.stream(cv.split(",")).toList()));
        return returnCvs;
    }

    @Override
    @Transactional
    public DummyConfigurableModelVo checkout(Integer id) {
        log.info("request param id {} :", id);
        if (id == null) {
            throw new DcmBusinessException("");
        }
        DummyConfigurableModel dummyConfigurableModel = dummyConfigurableModelService.getById(id);
        if (dummyConfigurableModel == null) {
            // 没有对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }

        if (dummyConfigurableModel.getCheckoutStatus() == 1 && StringUtils.isNotBlank(dummyConfigurableModel.getCheckoutBy())) {
            if (ContextUtil.getCurrentUser().equals(dummyConfigurableModel.getCheckoutBy())) {
                return getDcmById(id);
            } else {
                String number = dummyConfigurableModelService.getDcmNumberById(id);
                throw new DcmBusinessException(StringUtils.replace((StringUtils.replace(BusinessMsgEnum.DCM_HAS_BEEN_CHECKOUTED.getMessage(), "other users", dummyConfigurableModel.getCheckoutBy())), "number", number), BusinessMsgEnum.DCM_HAS_BEEN_CHECKOUTED.getCode());
            }
        }
        if (Boolean.FALSE.equals(dummyConfigurableModel.getLastestAll())) {
            // 不是最新的对象
            throw new DcmBusinessException(BusinessMsgEnum.DCM_IS_NOT_LASTEST);
        }
        Integer integer = dummyConfigurableModelService.newVersion(dummyConfigurableModel);
        log.info("checkout new id {} :", integer);
        return getDcmById(integer);
    }


    public List<DummyConfigurableModel> getDcmList(List<Long> ids) {
        List<DummyConfigurableModel> dcmList = new ArrayList<>();
        if (CollectionUtil.isEmpty(ids)) {
            return dcmList;
        }
        return dummyConfigurableModelService.listByIds(ids);
    }

    @Override
    public List<DcmLoadsheetDto> getDcmLoadsheetDtos(List<Long> ids) {
        List<DcmLoadsheetDto> loadsheetDtos = new ArrayList<>();
        List<DummyConfigurableModel> dcmList = getDcmList(ids);
        //查找所有masterId及对应name
        Set<Long> masterIds = dcmList.stream().map(DummyConfigurableModel::getMasterId).collect(Collectors.toSet());
        List<DummyConfigurableModelMaster> masterList = dummyConfigurableModelMasterService.listByIds(masterIds);
        Map<Long, String> masterMap = masterList.stream().collect(Collectors.toMap(DummyConfigurableModelMaster::getId, DummyConfigurableModelMaster::getName));

        //查找对应所有category
        Map<Long, List<DummyConfigurableModelCategory>> categoryMap = dummyConfigurableModelCategoryService.getCategoryByDcmIds(ids);
        List<Long> categoryIds = new ArrayList<>();
        for (List<DummyConfigurableModelCategory> list : categoryMap.values()) {
            if (CollectionUtil.isNotEmpty(list)) {
                categoryIds.addAll(list.stream().map(DummyConfigurableModelCategory::getId).toList());
            }
        }
        Map<Long, List<DummyConfigurableModelProductFeaturesDesc>> featuresDescMap = dummyConfigurableModelProductFeaturesDescService.getFeaturesDescMap(categoryIds);
        for (DummyConfigurableModel dcm : dcmList) {
            List<DummyConfigurableModelCategory> categories = categoryMap.get(dcm.getId());
            if (CollectionUtil.isNotEmpty(categories)) {
                for (DummyConfigurableModelCategory category : categories) {
                    List<DummyConfigurableModelProductFeaturesDesc> descs = featuresDescMap.get(category.getId());
                    for (DummyConfigurableModelProductFeaturesDesc desc : descs) {
                        DcmLoadsheetDto dto = new DcmLoadsheetDto();
                        dto.setCtoNumber(dcm.getReferenceCtoNumber());
                        dto.setOfferingName(masterMap.get(dcm.getMasterId()));
                        dto.setCategory(category.getCategoryName());
                        dto.setFeature(desc.getDescName());
                        loadsheetDtos.add(dto);
                    }
                }
            }
        }

        return loadsheetDtos;
    }

    @Override
    public void testModel() {
        DcmModelTest dcmModelTest = new DcmModelTest();
        dcmModelTest.setId(5L);
        dcmModelTest.setTypeId(210L);
        //根据Internal Name查询type
        TypeDefinitionModel typeDefinitionModel = typeAttributeExtService.getTypeDetail("model_test");
        //查询某个属性的GlobalAttributeId
        LocalAttributeModel localAttributeModel = typeAttributeExtService.getLocalAttribute(dcmModelTest.getTypeId(), "testLong");
        //保存属性
        typeAttributeExtService.saveAttributeValue(dcmModelTest, localAttributeModel.getGlobalAttributeId(), 120L);
    }

    @Override
    @Transactional
    public Long createDcmInUI(String productName, String geo, String ctoNumbers) {
        // 验证 productname存在 cto number存在
        ctoNumbers = ctoNumbers.replaceAll(" ", "");
        List<String> ctos = Arrays.stream(ctoNumbers.split(",")).distinct().toList();
        String ctoNumberString = CollectionUtil.join(ctos, ",");
        List<CtoCV> ctoCVS = ctoCVService.list(Wrappers.lambdaQuery(CtoCV.class).in(CtoCV::getCtoNumber, ctos));

        precheckRole(geo);

        ctos.stream().filter(ctoNumber -> !ctoCVS.stream().map(CtoCV::getCtoNumber).toList().contains(ctoNumber)).findAny().ifPresent(s -> {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_CTO_NUMBER_NOT_EXIST);
        });

        List<PortfolioOffering> list = portfolioOfferingService.lambdaQuery().eq(PortfolioOffering::getOfferingName, productName).list();

        if (CollectionUtil.isEmpty(list)) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_OFFERING_NAME_NOT_EXIST);
        }

        DummyConfigurableModel dcm = dummyConfigurableModelService.getDcmByNameAndGeo(productName, geo);

        if (dcm != null) {
            throw new DcmBusinessException(BusinessMsgEnum.OFFERING_NAME_AND_GEO_HAS_EXISTED);
        }

        List<CtoCVsDto> cvsDtos = ctoCVService.getCVListByCtoNamesAndProductSegment(ctoNumberString, list.get(0).getProductSegment(), geo);

        List<DcmLoadsheetDto> dcmLoadsheetDtos = Lists.newArrayList();
        cvsDtos.stream().collect(Collectors.groupingBy(CtoCVsDto::getCategory)).forEach((key, value) -> {
            value.stream().map(CtoCVsDto::getValues).flatMap(List::stream).distinct().forEach(cv -> {
                DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
                dcmLoadsheetDto.setOfferingName(productName);
                dcmLoadsheetDto.setCategory(key);
                dcmLoadsheetDto.setFeature(CTO_PREFIX + cv);
                dcmLoadsheetDto.setCtoNumber(ctoNumberString);
                dcmLoadsheetDtos.add(dcmLoadsheetDto);
            });
        });
//        cvsDtos.forEach(cvsDto -> cvsDto.getValues().forEach(value -> {
//            DcmLoadsheetDto dcmLoadsheetDto = new DcmLoadsheetDto();
//            dcmLoadsheetDto.setOfferingName(productName);
//            dcmLoadsheetDto.setCategory(cvsDto.getCategory());
//            dcmLoadsheetDto.setFeature(CTO_PREFIX + value);
//            dcmLoadsheetDto.setCtoNumber(ctoNumberString);
//            dcmLoadsheetDtos.add(dcmLoadsheetDto);
//        }));

        ArrayList<DcmLoadsheetDto> loadsheetDtos = dcmLoadsheetDtos.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getCategory() + o.getFeature()))), ArrayList::new));
        DummyConfigurableModelDto modelDto = new DummyConfigurableModelDto();
        modelDto.setName(productName);
        modelDto.setProductSegment(list.get(0).getProductSegment());
        modelDto.setBusinessSegment(list.get(0).getBusinessSegment());
        modelDto.setGeo(geo);
        modelDto.setReferenceCtoNumber(ctoNumbers);
        modelDto.setDummyConfigurableModelCatagoryDtos(Lists.newArrayList());
        DummyConfigurableModelDto dummyConfigurableModelDto = handleDcmLoadsheetDto(modelDto, loadsheetDtos);
        DummyConfigurableModelDto newDcmModel = handelDataNew(dummyConfigurableModelDto, false);
        newDcmModel.setVersionRemark("Created by UI");
        return createDcm(newDcmModel);
    }

    private void precheckRole(String geo) {
        List<String> geos = getGeos();
        if (!geos.contains(geo)) {
            throw new DcmBusinessException("Please add " + geo + " Editor permissions first.", BusinessMsgEnum.DCM_NOT_GEO_EDITOR.getCode());
        }
    }

    @Override
    public Set<String> getEditPermissionGeos() {
        UserAuthDto userAuthorities = iTeamUserService.getUserAuthorities(ContextUtil.getCurrentUser(), false);
        List<ConfigurationGeoMapping> list = configurationGeoMappingService.list();
        List<TeamGroup> groups = userAuthorities.getGroups();

        // * 并且是GROUP_IT_ADMIN时返回所有GEO, 这是唯一的特殊情况。否则按实际的配置返回
        List<ConfigurationGeoMapping> itAdminList = list.stream().filter(geoMapping -> NAME_GROUP_ITADMIN.equalsIgnoreCase(geoMapping.getGroupName())
                && StringPool.ASTERISK.equals(geoMapping.getGeo())).toList();
        if (CollUtil.isNotEmpty(itAdminList) && groups.stream().map(TeamGroup::getGroupName).toList().contains(NAME_GROUP_ITADMIN)) {
            return list.stream().filter(geoMapping -> !StringPool.ASTERISK.equals(geoMapping.getGeo()))
                    .map(ConfigurationGeoMapping::getGeo).collect(Collectors.toSet());
        } else {
            return list.stream()
                    .filter(geoMapping -> groups.stream().map(TeamGroup::getGroupName).toList().contains(geoMapping.getGroupName())
                            && geoMapping.getGroupName().endsWith(NAME_GROUP_SUFFIX_EDITOR) && !StringPool.ASTERISK.equals(geoMapping.getGeo()))
                    .map(ConfigurationGeoMapping::getGeo).collect(Collectors.toSet());
        }
    }

    @Override
    public void removeDcmById(Integer id) {
        /**
         * precheck id 不为空
         * precheck id 不为最新
         */
        if (id == null) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_ID_IS_NULL);
        }
        DummyConfigurableModel dcm = dummyConfigurableModelService.getById(id);
        if (dcm == null) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_NOT_EXIST);
        }
        if (!dcm.getLastestAll()) {
            throw new DcmBusinessException(BusinessMsgEnum.DCM_IS_NOT_LASTEST);
        }
        /**
         * 删除最新状态
         */
        DummyConfigurableModel dummyConfigurableModel = new DummyConfigurableModel();
        dummyConfigurableModel.setLastestAll(false);
        dummyConfigurableModel.setState("Deleted");
        dummyConfigurableModel.setDeleted(1);
        boolean updated = dummyConfigurableModelService.lambdaUpdate().eq(DummyConfigurableModel::getId, id).update(dummyConfigurableModel);
        /**
         * 修改name改为 name_deleted
         */
        DummyConfigurableModelMaster dcmMaster = dummyConfigurableModelMasterService.getById(dcm.getMasterId());
        dcmMaster.setName(dcmMaster.getName() + "_deleted");
        dummyConfigurableModelMasterService.lambdaUpdate().eq(DummyConfigurableModelMaster::getId, dcmMaster.getId()).update(dcmMaster);
        if (updated) {
            Map<String, Object> map = Maps.newHashMap();
            map.put("number", dcmMaster.getNumber());
            map.put("name", dcmMaster.getName());
            map.put("GEO", dcm.getGeo());
            map.put("state", "Deleted");
            KafkaHelper.publish("dcm", JSONUtil.toJsonStr(map));
        }
    }


}
